Percentual of Grand Total - help.

[bolabuga]

Hello everyone.

I have the following sample table:

REF	PROD	REVEN
jan/17	A	10
jan/17	B	20
jan/17	C	30
fev/17	A	7
fev/17	B	8
fev/17	C	9
mar/17	A	5
mar/17	B	30
mar/17	C	21
abr/17	A	56
abr/17	B	21
abr/17	C	4
mai/17	A	8
mai/17	B	9
mai/17	C	13
jun/17	A	25
jun/17	B	32
jun/17	C	14
jul/17	A	65
jul/17	B	89
jul/17	C	14
ago/17	A	1
ago/17	B	36
ago/17	C	54
set/17	A	65
set/17	B	5
set/17	C	47

Which provides me with the scenario on the screen shot.

TOT.REVENUE = SUM(Plan1[REVEN])

SUBTOT.REVENUE = CALCULATE([TOT.REVENUE];ALL(Plan1[REF]))

% REVENUE = DIVIDE([TOT.REVENUE];[SUBTOT.REVENUE])

Im able to get the % for A, B or C using the "% REVENUE" metric, but not in the way i need. Its considering the subtotal. and i need the result thas being showed on the screen shot on the left side (colored one), where "jan/17 - A" is being divided by the grand total, and the final result is everyone % summed being 100%.  

[BetterCallFrank · Accepted solution]

Hi @bolabuga

maybe try: 

TOT.REVENUE = SUM(Plan1[REVEN])
SUBTOT.REVENUE = CALCULATE([TOT.REVENUE];ALL(Plan1[REF]))
Grand Total = CALCULATE( [TOT.REVENUE]; ALL(Plan1[REF]; ALL(Plan1[PROD]) )
% REVENUE = DIVIDE([TOT.REVENUE];[SUBTOT.REVENUE])
%GrandTotal = DIVIDE([TOT.REVENUE];[Grand Total])

HTH,

Frank

[bolabuga]

Thanks BetterCallFrank, it worked well, i didnt think i could use 2 "all" in sequence.

Just correcting the "Grand Total" metric, that was missing 1 ")" in "ALL(Plan1[REF]"

TOT.REVENUE = SUM(Plan1[REVEN])
SUBTOT.REVENUE = CALCULATE([TOT.REVENUE];ALL(Plan1[REF]))
Grand Total = CALCULATE( [TOT.REVENUE]; ALL(Plan1[REF]); ALL(Plan1[PROD]) )
% REVENUE = DIVIDE([TOT.REVENUE];[SUBTOT.REVENUE])
%GrandTotal = DIVIDE([TOT.REVENUE];[Grand Total])