cancel
Showing results for
Did you mean:
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

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.

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
Super User I

## Re: How to Sum measures in my example.

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.

Proud a to be a Datanaut!
Frequent Visitor

## Re: How to Sum measures in my example.

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

Regular Visitor

## Re: How to Sum measures in my example.

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!

Frequent Visitor

## Re: How to Sum measures in my example.

Actual file I have uploded here.

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

Thanks

Super User I

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

Proud a to be a Datanaut!
Frequent Visitor

## Re: How to Sum measures in my example.

@vinaypugalia

Hi there,

Did you had any luck with DAX for my example ?

Thanks

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

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

Hi,

It Works!!! 🙂

Thanks a lot!

Frequent Visitor

## Re: How to Sum measures in my example.

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors