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=JNTWhJ
My Table Setup:
What I am trying to achieve:
I would like to display, total amount of boxes and pallets needed each week, by selecting dates in the slicer.
Orders - this table tells me how many finished goods I have to produce
BOM - this table tells me what are the material / component quantities to produce one finished good.
Storage Configuration - this table is telling me qty per box and qty per pallet for each material.
Thanks for help.
what i am trying to understand here is what you aren't able to do, i can see you have summed box and if i put a date slicer in it shifts the amounts.
what are you expecting that is different from what you are showing here.
At this moment SUM of the measures for Boxes is not what I want.
Current: = 400
I would like: 20+30+22+200+300+220+24+31+48 = 895
I tried to create measure:
I see what you are expecting but you will never get it this way. The basic reason being the context of your measure changes when plotted as a card Vs when you plot it in the table.
For e.g. the MAX() which you will get for each row will be different than the MAX() which you will get for the card.
I can get you the right DAX if you can share the sample pbix file so that I need to prepare the sample data. And it will help others too if they want to help you.
Looking forward to hear from you.
Thanks for reply and explanation, very helpful.
Actual file I have uploded here.
@vinaypugalia correct, you got to this before me so i will leave you to do it rather than duplicating effort.
Did you had any luck with DAX for my example ?
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.
It Works!!! 🙂
Thanks a lot!
Unfortunately your solution is not accurate. Yes, both numbers matching (card and totals), but calculation it self is not correct.
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
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!