How to compare a measure on different granularities

17 Views Asked by At

I have a fact table "Affects" that contains columns as number of ordre OA, matierial, color of material and quantity affected per week. each OA has many material-color of material SO for each material, color of material, we have an affected quantity per week For example; for OA= 1234 for week 1, I have quantity for Mat 1=10, quantity for Mat 2=0,quantity for Mat 1=8 for week 2, I have quantity for Mat 1=5, quantity for Mat 2=0,quantity for Mat 1=5, for week 3, I have quantity for Mat 1=8, quantity for Mat 2=2,quantity for Mat 1=5,

so for each week, for OA=1234 the material with the least quantity is: Mat2 I have a measure that calculates the minumum quantity per week and per OA-material

Quantity minimum = 
Var Min_affect =

   MINX(SUMMARIZE('Affects','Affects[OA]',Affects[Material]),[Quantity])

return  Min_affect 

How can I create a flag ( a measure) to identify for each week and each OA, the materials with the least quantity? Thank you for your help

Already tried to create a mesure that calculate the minimum per OA and compare to Quantity minimum for each week, if Quantity minimum OA= Quantity minimum then return "X" else Blank() but it does not work Quantity minimum OA = Var Min_affect =

MINX(SUMMARIZE('Affects','Affects[OA]']),[Quantity])

return Min_affect

0

There are 0 best solutions below