Forum Discussion

data_buzz's avatar
data_buzz
Frequent Visitor
7 years ago

How to Sum measures in my example.

Hi All,    I am trying to SUM measures and display them as cards (Pic Below), but I can't figure out the way how to sum measures in my example.   Link to Power Bi file - https://gofile.io/?c=JNTWh...
Need Help
v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi data_buzz ,

By my tests with your sample pbix, you need to modify your measure [Material Qty]  like below.

Material Qty = 
MAX ( Orders[Qty] ) * CALCULATE ( SUM ( 'Bill of material BOM'[Qty Required] ) )

Then create the [Boxes] and [Pallets ]use SUMX.

Boxes = SUMX('Orders', [Material Qty]/MAX('Storage Configuration'[Box Qty]))
Pallets = SUMX('Orders', [Material Qty]/MAX('Storage Configuration'[Pallet Qty]))

Here is the output.

Best Regards,

Cherry

 

data_buzz's avatar
data_buzz
Frequent Visitor
7 years ago

v-piga-msft 

 

Hi, 

Unfortunately your solution is not accurate. Yes,  both numbers matching (card and totals), but calculation it self is not correct. 

For example: 

 

On the first line (from your example), Salt, material qty is 500 and in your calculation its 20 boxes and 3 pallets. 

But actualy it should be 100 boxes and 5 pallets, since in pallet configuration table 5 is box quantity and 100 is pallet quantity. 

 

In yours Boxes   500 / 25 = 20

Should be Boxes 500 / 5 = 100

 

Looks like instead of Vlookup it takes Max number from table. Is there a way instead of MAX to use some sort of Lookup function ? 

 

Hope it makes sense

Thanks