cancel
Showing results for
Did you mean:
emudria Member

## DAX - sum the measure values in total and Not the calculated values at totals

Hi Community,

Power Bi Sample file

I need help with this measure. I got a dataset, with Items sold(ItemID), with quantity(quantity) sold for each item.

we also got a weightage(product weightage) of each itemID.

I am trying to calculate a mix indicator for all these itemIDs based upon the QTY sold and the weightage for each item. I am calculating the values based on following formula

```mix indicator calculted =
VAR item_sum =
SUM ( 'dump data test01'[Quantity] )
VAR total_sum =
CALCULATE (
SUM ('dump data test01'[Quantity]  ),
ALL ( 'dump data test01'[ItemID] )
)
var result =
DIVIDE ( item_sum, total_sum )*SUM('dump data test01'[product weightage])

RETURN
result```

This gives me a value, which is working fine on row level. Though on the totals line, it is again calculating the mix. I on the other hand, want the total to show the sum of mix for all values, which in this example should be around 15% and not 2923%.

Report attached here with sample data and formula.

Any direction in this regard would be a great help.

Regards

emudria

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

## Re: DAX - sum the measure values in total and Not the calculated values at totals

Hi @emudria

You can add a new measure:

`Measure 2 = SUMX(VALUES('dump data test01'[ItemID]),[mix indicator calculted])` Best regards,

Dina Ye

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

## Re: DAX - sum the measure values in total and Not the calculated values at totals

Hi @emudria

You can add a new measure:

`Measure 2 = SUMX(VALUES('dump data test01'[ItemID]),[mix indicator calculted])` Best regards,

Dina Ye

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

## Re: DAX - sum the measure values in total and Not the calculated values at totals

THANK YOU so much Dina. works perfectly. I had spent so many hours trying to do that. Thanks again.

1 - what is happening in this formula

2 - why doesnt it work if instead of creating a new measure, I put the returned variable 'result' in this formula in same measure

ie

measure =

`SUMX(VALUES('dump data test01'[ItemID]),result)`

Announcements Learn the answers to some of the questions asked during the Amanda Triple A event. #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 147 members 1,744 guests
Recent signins:
• aravindn • KAyari • Derek_Stannett • ldittmann970 • chandra_0202 • aashishj9 • atomar070 • Ritzel • SeanKneipp • RobsTechni • binto0002 • SRESP • MF2003 • matteo_dpi 