cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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.

2019-07-16 11 27 52.png

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
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

You can add a new measure:

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

5.png

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.

View solution in original post

3 REPLIES 3
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

You can add a new measure:

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

5.png

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.

View solution in original post

what if I have another field along with item such as "X", will this formula still work?

Anonymous
Not applicable

@v-diye-msft 

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

Can I please ask two things:

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)

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors