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

Struggling with the Measure Totals

Hi All,

 

I've read through many of the articles/posts already made on Measure totals not adding up correctly. I've tried to apply but I cannot get it to work and hoping someone might be able to help me.

 

Essentially I have the following data:

 

ClientPersonYearRevenueYear 1 PctYear 2 Pct
AA20171000.60.4
AA20181500.60.4
AB20171000.40.6
AB20181500.40.6

 

And I'm trying to get to this result:

 

ClientPersonRevenue Year 1Revenue Year 2Carry RevenueNew RevenueCarry Revenue AllocNew Revenue AllocYear 1 AllocationYear 2 Allocation
AA1001501005040306070
AB1001501005060204080
TOTAL20030020010010050100150

 

I've deployed the following measures:

 

Revenue Year 1 = calculate(sum([Revenue]),[Year]=2017) 

Revenue Year 2 = calculate(sum([Revenue]),[Year]=2018) 

 

Carry Revenue = if([Revenue Year 1]>[Revenue Year 2],[Revenue Year 2],[Revenue Year 1])

New Revenue = if([Revenue Year 2]>[Revenue Year 1],[Revenue Year 2]-[Revenue Year 1],0)

 

Carry Revenue Alloc: [Carry Revenue]*max(Year 2 Pct)

New Revenue Alloc: [New Revenue]*max(Year 1 Pct])

 

Year 1 Allocation: [Revenue Year 1] * max(Year 1 Pct])

Year 2 Allocation: [Carry Revenue Alloc] + [New Revenue Alloc]

 

I'm able to get the line by line to be accurate as above, but the totals are not adding up properly. I'm probably getting tripped up where I am using MAX to apply the percentage to multiply against, but after a couple of different attempts I just haven't been able to figure this out.

 

Appreciate any help, thank you!

 

1 ACCEPTED SOLUTION

To close the loop, I ended up creating a new table and summarizing the measures. It's an extra step, but it works. Thanks all

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

So, you can read this but you may have already found it: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

 

But, one of the most fool proof ways that I have gotten this to work is to keep all of your individual measures and for each fo them write a corresponding measure like this:

 

Revenue Year 1 Total = 
VAR __table = SUMMARIZE('Table',[Client],[Person],"__revenueyear1",[Revenue Year 1])
RETURN
IF(HASONEVALUE('Table'[Client]),[Revenue Year 1],SUMX(__table,[__revenueyear1]))

Something along those lines. So, basically you are summarizing your table exactly has it appeas in the matrix/table you have and including in the summarization the individual measure that you wrote. Then, if you are on an individual row, you simply use that measure. Alternatively, you do a SUMX (or other aggregation) across your summarized table/matrix.

 

You might find this one interesting as well, Matrix Measure Total Triple Threat Rock & Roll:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

 


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

Thanks, I did read the first post but trying to apply its principals are proving elusive to me.

 

Without changing anything, I finally realize that the existing formulas, as you stated in your other post, are acting exactly as intended. The calculated total on the measure is applying the Max percentage to the total revenue column, e.g. 673046.19 * 0.9.

 

I think one way to solve for this is to pivot the rows to columns in the original query, but I was hoping DAX could help me avoid this since it takes awhile to load.

 

 ALLOC_YR1_PCTALLOC_YR2_PCTALLOC_YR1_AMTALLOC_YR2_AMTCARRY_YR2_EXT_ALLOCCARRY_YR2_NEW_ALLOCTOTAL_REV_YR1TOTAL_REV_YR2
 0.900.6011689.3649714.817792.9041921.9112988.1759568.07
 0.100.401298.829853.265195.274657.9912988.1759568.07
 0.000.000.000.000.000.0012988.1759568.07
 0.000.000.000.000.000.0012988.1759568.07
 0.000.000.000.000.000.0012988.1759568.07
 0.900.60109458.9616554.4816554.480.00121621.0627590.81
 0.000.000.000.000.000.00121621.0627590.81
 0.000.000.000.000.000.00121621.0627590.81
 0.000.000.000.000.000.00121621.0627590.81
 0.100.4012162.1111036.3211036.320.00121621.0627590.81
Correct TOTALS  134609.2487158.8740578.9846579.89673046.19435794.37
  0.90 MAX 0.60 MAX      
What BI shows as the total  605741.6261476.6    

Hi @ijmiller16,

 

Please refer to the case.

 

Regards,

Frank

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

Thanks, this gets me closer but it changes the values just slightly. I'm not sure why.

 

For example sum value is 210,460.33

 

SUMX makes it 212,321.99

To close the loop, I ended up creating a new table and summarizing the measures. It's an extra step, but it works. Thanks all

Hi @ijmiller16,

 

Kindly mark the answer as solution to close the case please. Thanks in advance.

 

Regards,

Frank

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

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.