cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ijmiller16 Frequent Visitor
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

Accepted Solutions
ijmiller16 Frequent Visitor
Frequent Visitor

Re: Struggling with the Measure Totals

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

6 REPLIES 6
Super User
Super User

Re: Struggling with the Measure Totals

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

 


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

Proud to be a Datanaut!


ijmiller16 Frequent Visitor
Frequent Visitor

Re: Struggling with the Measure Totals

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    
Community Support Team
Community Support Team

Re: Struggling with the Measure Totals

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 other members find it more quickly.
ijmiller16 Frequent Visitor
Frequent Visitor

Re: Struggling with the Measure Totals

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

ijmiller16 Frequent Visitor
Frequent Visitor

Re: Struggling with the Measure Totals

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

Highlighted
Community Support Team
Community Support Team

Re: Struggling with the Measure Totals

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 other members find it more quickly.