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:
|Client||Person||Year||Revenue||Year 1 Pct||Year 2 Pct|
And I'm trying to get to this result:
|Client||Person||Revenue Year 1||Revenue Year 2||Carry Revenue||New Revenue||Carry Revenue Alloc||New Revenue Alloc||Year 1 Allocation||Year 2 Allocation|
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!
Solved! Go to Solution.
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:
Proud to be a Datanaut!
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.
|0.90 MAX||0.60 MAX|
|What BI shows as the total||605741.6||261476.6|
Kindly mark the answer as solution to close the case please. Thanks in advance.