Forum Discussion

MiBel_'s avatar
MiBel_
Copper Contributor
Jan 09, 2022
Solved

Pivot table from combined datasets

Hi all, I was thinking I was getting used to using a pivot table with multiple source data, but I got lost in a simple problem, where I can not simply find a solution.   I split my problem into tw...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 09, 2022

    MiBel_ 

    Let keep relationships as

    and create two measures

    Cost Delivered:=SUMX( Results, Pricelist[Součet Price] )

    and

    OnTime Delivered:=CALCULATE( [Cost Delivered], TREATAS( VALUES(OnTime[ID]), Results[ID] ))

    First measure returns

    second one

    I'm not sure why in Targets is 10 for John - both 01-01 and 02-02 are in time

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 09, 2022

MiBel_ 

Let keep relationships as

and create two measures

Cost Delivered:=SUMX( Results, Pricelist[Součet Price] )

and

OnTime Delivered:=CALCULATE( [Cost Delivered], TREATAS( VALUES(OnTime[ID]), Results[ID] ))

First measure returns

second one

I'm not sure why in Targets is 10 for John - both 01-01 and 02-02 are in time

MiBel_'s avatar
MiBel_
Copper Contributor
Jan 09, 2022

SergeiBaklan 

Thank you for your response. The wrong number may be a math error.

 

For me is interesting, that this problem can not be solved via "connecting" tables, but is needed an additional row, just a "better" xlookup.

But thank you very much.

Michal

Resources