Forum Discussion
Measure with different granularities
Dear Community,
I have a problem using a fake example to try to get to my answer. I create a measure , which is a multiplication between ingredient and price (two fact tables) via a material dimension table. Problem is, the ingredient fact table uses different granularities for the receipe. Therefore, the solid relationship between the fact and the dimension table ignores the higher granularity. Here for better understanding:
The "milk" granularity exist in the "DIM Material" table, however the tables are connected via "Material".
What I need is the following:
Whenever there is a direct match, then use the prices accordingly from the "FACT TABLE Prices", if not, then take the average of the "DIM Material Class", in this case Milk = 1.25 (= the green highlighted cells).
I believe a sort if IF-statement must be used...
I don't know how to realize this...help! Thx!
Using two SUMX actually solved the problem.
3 Replies
I_LOVE_POWER_BI , based on what I got so far
Refer these Meausres
Avg Price = Average(FactPrice[Price])
Qty = Sum(FactReceipe[Qty]) // excat column I am not sure
total= sumx(Values(Material[Material Group]) , [Avg Price] * [Qty]))
Thanks! It technically works, however you have to imagine, that the Prices table includes many prices for the different material classes. But normally I want the exact material price to be used, only if the recipe table uses a material group the average shall be used. I updated the screenshot accordingly:
Goal is to use the green marked prices:
Using two SUMX actually solved the problem.
