Struggling with the Measure Totals

[ijmiller16]

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:

Client	Person	Year	Revenue	Year 1 Pct	Year 2 Pct
A	A	2017	100	0.6	0.4
A	A	2018	150	0.6	0.4
A	B	2017	100	0.4	0.6
A	B	2018	150	0.4	0.6

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
A	A	100	150	100	50	40	30	60	70
A	B	100	150	100	50	60	20	40	80
TOTAL	200	300	200	100	100	50	100	150

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!

[Greg_Deckler]

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

[ijmiller16]

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_PCT	ALLOC_YR2_PCT	ALLOC_YR1_AMT	ALLOC_YR2_AMT	CARRY_YR2_EXT_ALLOC	CARRY_YR2_NEW_ALLOC	TOTAL_REV_YR1	TOTAL_REV_YR2
	0.90	0.60	11689.36	49714.81	7792.90	41921.91	12988.17	59568.07
	0.10	0.40	1298.82	9853.26	5195.27	4657.99	12988.17	59568.07
	0.00	0.00	0.00	0.00	0.00	0.00	12988.17	59568.07
	0.00	0.00	0.00	0.00	0.00	0.00	12988.17	59568.07
	0.00	0.00	0.00	0.00	0.00	0.00	12988.17	59568.07
	0.90	0.60	109458.96	16554.48	16554.48	0.00	121621.06	27590.81
	0.00	0.00	0.00	0.00	0.00	0.00	121621.06	27590.81
	0.00	0.00	0.00	0.00	0.00	0.00	121621.06	27590.81
	0.00	0.00	0.00	0.00	0.00	0.00	121621.06	27590.81
	0.10	0.40	12162.11	11036.32	11036.32	0.00	121621.06	27590.81
Correct TOTALS			134609.24	87158.87	40578.98	46579.89	673046.19	435794.37
	0.90 MAX	0.60 MAX
What BI shows as the total			605741.6	261476.6

[v-frfei-msft]

Hi @ijmiller16,

Please refer to the case.

Regards,

Frank

[ijmiller16]

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

[v-frfei-msft]

Hi @ijmiller16,

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

Regards,

Frank