Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
data_buzz
Frequent Visitor

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=JNTWhJ

 

Report.PNG

 

My Table Setup: 

 

Table Relationships 1.PNG

 

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. 

 

My tables

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. 

 

My Measures

Material Qty = MAX(Orders[Qty])*MAX('Bill of material BOM'[Qty Required])   ------This measure is telling me qty needed, for each material, to produce works order. 
 
Boxes = [Material Qty]/MAX('Storage Configuration'[Box Qty]) -------- This measure converts above material qty in to the amount of boxes 
 
Pallets = [Material Qty]/MAX('Storage Configuration'[Pallet Qty]) -------- as above but in pallets. 

 

 

Thanks for help.

 

9 REPLIES 9
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Capture.PNG

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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

@v-piga-msft 

Hi, 

It Works!!! 🙂

 

Thanks a lot! 

 

vanessafvg
Super User
Super User

@data_buzz 

 

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.  





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg 

 

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: 

       

Box Totals =
SUMX('Bill of material BOM',
     [Boxes])  ------- result not what I want
 
...then I have tried: 
 
SUMX(
    VALUES('Bill of material BOM'[Components Nr]),
     [Boxes])) -------- result not what I want. 
 
....run out of options unfortunately.
 
Thanks

 

 

 

Thanks

@data_buzz 

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!

@vinaypugalia

 

Hi there, 

 

Did you had any luck with DAX for my example ? 

 

Thanks

 

@vinaypugalia correct, you got to this before me so i will leave you to do it rather than duplicating effort.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vinaypugalia 

 

Thanks for reply and explanation, very helpful. 

 

Actual file I have uploded here. 

 

https://gofile.io/?c=hyLfpo

 

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.