Forum Discussion

I_LOVE_POWER_BI's avatar
I_LOVE_POWER_BI
Icon for Helper III rankHelper III
4 years ago
Solved

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 dime...
Need Help
  • I_LOVE_POWER_BI's avatar
    I_LOVE_POWER_BI
    4 years ago

    Using two SUMX actually solved the problem.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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]))

I_LOVE_POWER_BI's avatar
I_LOVE_POWER_BI
Icon for Helper III rankHelper III
4 years ago

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:

 

 

  • I_LOVE_POWER_BI's avatar
    I_LOVE_POWER_BI
    Icon for Helper III rankHelper III
    4 years ago

    Using two SUMX actually solved the problem.