cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
data_buzz Frequent Visitor
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
vanessafvg Super Contributor
Super Contributor

Re: How to Sum measures in my example.

@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.  


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
data_buzz Frequent Visitor
Frequent Visitor

Re: How to Sum measures in my example.

@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

Highlighted
vinaypugalia Regular Visitor
Regular Visitor

Re: How to Sum measures in my example.

@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!

data_buzz Frequent Visitor
Frequent Visitor

Re: How to Sum measures in my example.

@vinaypugalia 

 

Thanks for reply and explanation, very helpful. 

 

Actual file I have uploded here. 

 

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

 

Thanks

vanessafvg Super Contributor
Super Contributor

Re: How to Sum measures in my example.

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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
data_buzz Frequent Visitor
Frequent Visitor

Re: How to Sum measures in my example.

@vinaypugalia

 

Hi there, 

 

Did you had any luck with DAX for my example ? 

 

Thanks

 

Community Support Team
Community Support Team

Re: How to Sum measures in 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.

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.
data_buzz Frequent Visitor
Frequent Visitor

Re: How to Sum measures in my example.

@v-piga-msft 

Hi, 

It Works!!! Smiley Happy

 

Thanks a lot! 

 

data_buzz Frequent Visitor
Frequent Visitor

Re: How to Sum measures in my example.

@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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 253 members 2,798 guests
Please welcome our newest community members: