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

Grand total SUM of SUMX results

 

Hello,

 

Is there a way to show the total sum of another measure that's using SUMX?

For each row the values are correct but the total is wrong for the field called Impact.

 

 

Impact shows: 32, 527 (Wrong)

 

Impact total should be: 32, 473

I want the total to show the sum of all values above total of Impact so in this case (1,900+15,440+1,800+13,333)

 

Data.PNG

 

 

The user can also slice on Date, Supplier and Item so the total should be dynamic.

 

Measures used:

 

Comp:

SUMX('fact';CALCULATE(DIVIDE(SUM('fact'[SumGLAmount]);SUM('fact'[SumGL/PO Qty]))))

 

Base:

CALCULATE(SUMX('fact';CALCULATE(DIVIDE(SUM('fact'[SumGLAmount]);SUM('fact'[SumGL/PO Qty]))));SAMEPERIODLASTYEAR('Date'[Date]))

 

Weighted C:

SUM('fact'[WeightedPMI])

 

Weighted B:

CALCULATE(SUM(fact[WeightedPMI]);SAMEPERIODLASTYEAR('Date'[Date]))

 

Weighted Change CY vs LY %:

DIVIDE(([Weighted C]-[Weighted B]);[Weighted B])

 

Ongoing:

DIVIDE(([Comp]-[Base]);[Base])

 

Impact:

IF(HASONEFILTER('vtotalcost'[Item RM]);
                    [Base]*(1+[Weighted Change CY vs LY %]);
                    SUMX(VALUES('vtotalcost'[Item RM]);
                        [Base]*(1+[Weighted Change CY vs LY %]))
                )

10 REPLIES 10
v-lili6-msft
Community Support
Community Support

hi, @Kinesaren

Total result is calculate base on the whole table

for example:

basic data and expected result

7.JPG

the create measure

Measure = DIVIDE(CALCULATE(SUM(Table1[Qty])),CALCULATE(SUM(Table1[week])))

Normal result:

8.JPG

total is (10+12+15)/(2+6+15)=1.61 not 5+2+1=8

 

then you need to use this measure and basic table to create a temporary dynamic table and use SUMX to create a measure like below:

Measure 2 = 
var _table=SUMMARIZE(Table1,Table1[ID],"_per",DIVIDE(CALCULATE(SUM(Table1[Qty])),CALCULATE(SUM(Table1[week]))) ) return
SUMX(_table,[_per])

9.JPG

IF not your case, Please share some data sample and the formula and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading

 

 

 

Best Regards,

Lin

 

 

 

 

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

Hello @v-lili6-msft

Thank you for answering.

However, the value 'impact' is already a calculated measure and when I'm trying to use it in the 'Measure 2' statement with the summarize syntax it will only return blank results.

 

I have provided the sample file here:

https://www.dropbox.com/sh/9lpz5uo41xhpyl8/AABJLREFUZz_6PrUe8rkT9f9a?dl=0

 

The expected output should be 32, 473 (Sum of all single 'Impact' values)

Sample.PNG

I will try to experiment with the temporary dynamic table approach.

Thank you!

Greg_Deckler
Super User
Super User

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

 

Now, the general fix for this that I use is, and I'm making a few assumptions here:

 

Impact Single = [Base]*(1+[Weighted Change CY vs LY %]);

Impact = IF(HASONEVALUE('vtotalcost'[Item RM]);
                    [Impact Single]
                    SUMX(
                         SUMMARIZE('vtotalcost',[Supplier Number],[Item Number],[Item RM],"__Impact",[Impact Single]),
                         [__Impact]
                     )
                )

So, essentially, write your measure to work for a single row in a table or matrix. Then, write another measure that uses that measure for single rows but essentially recreates the table/matrix in memory (using your measure for single rows) and then do a SUMX across that table. 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hello @Greg_Deckler

Thank you for answering!

 

Your measure 'Impact' is returning an incomplete statement but I understand what you are trying to achieve.

I will continue to try with the tempory dynamic table approach.

 

Thank you!

 

Sorry, missed a comma. Check out my newly created Quick Measure, Measure Totals, The Final Word:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

 

Impact Single = [Base]*(1+[Weighted Change CY vs LY %])

Impact = IF(HASONEVALUE('vtotalcost'[Item RM]);
                    [Impact Single];
                    SUMX(
                         SUMMARIZE('vtotalcost',[Supplier Number],[Item Number],[Item RM],"__Impact",[Impact Single]);
                         [__Impact]
                     )
                )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hello @Greg_Deckler

 

Your article was very helpful in understanding the problem I am having, so thank you for that.

 

However, I am still having problem on a total level and I suspect it is something with the Time Intelligence functions that I am using. (The single measure is using CALCULATE together with SAMEPERIODLASTYEAR and a date table.

If I remove the measures where I have Time Intelligence DAX involved then the total level is all good but I am too dependent on those.

Do you have a second opinion about this? @Greg_Deckler

 

I am having exactly the same issue. @Kinesaren, have you been able to resolve this?

PattemManohar
Community Champion
Community Champion

@Kinesaren Please post the same sample data that can be copied





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Hello @PattemManohar

Thank you for answering!

 

I have provided the sample file here:

https://www.dropbox.com/sh/9lpz5uo41xhpyl8/AABJLREFUZz_6PrUe8rkT9f9a?dl=0

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.