Pivot table from combined datasets

[MiBel_]

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 two tasks

1) The target is to have a pivot table showing the total price, each worker has made on his products.

I don't get, why I can debug it. My steps were: connect table results (with 2 rows = worker and product) with table pricelist (rows product and price) and it is not working

2)  Target 2 is to have a separate table of products, which were delivered on time. I need the pivot table showing who has made how much on his products on time. There are only 2 rows (ID and product)

My steps were the following: create a new row, to be able to filter the product that was made on time and afterwards use the same steps as by task 1. I have also tried to connect this table with "results" via ID and with "pricelist" via name, but have not worked either.

Thank you very much for helping. I have spent a lot of time trying to find a solution myself, but I feel desperate, not solving such a simple task.

I hope, description is understandable, thank you very much for helping,

I am adding a simplified test file.

Best regards

Michal

link to file: https://we.tl/t-gGRFoyjWep 

[#2 · Accepted solution]

@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

[#3]

@Sergei Baklan 

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