Forum Discussion
Percent of Total
Greg_Deckler, I think you meant to use CALCULATE() or switched the order there.
TotalQuantity = SUM( 'Table'[Quantity] ) % Total = [TotalQuantity] / CALCULATE( [TotalQuantity] ,ALL( 'Table' ) ) ***OR*** TotalQuantity = SUM( 'Table'[Quantity] ) % Total = [TotalQuantity] / SUMX( ALL( 'Table' ) ,'Table'[Quantity] )Either construction would work, but I'd lean toward the former idiom.
In theory, it would be something like the following (as a measure):
= SUM([Quantity]) / SUMX([Quantity],ALL(table))
The idea is that the first SUM should get filtered by the row context but you are specifically including ALL rows in the second SUM(X). SUMX adds the ability to modify the filter context when summing.
Greg_Deckler, I think you meant to use CALCULATE() or switched the order there.
TotalQuantity =
SUM( 'Table'[Quantity] )
% Total =
[TotalQuantity]
/ CALCULATE(
[TotalQuantity]
,ALL( 'Table' )
)
***OR***
TotalQuantity =
SUM( 'Table'[Quantity] )
% Total =
[TotalQuantity]
/ SUMX(
ALL( 'Table' )
,'Table'[Quantity]
)Either construction would work, but I'd lean toward the former idiom.
greggyb Thanks for putting this together so succinctly. In applying your solution I found that your first expression should actually be:
TotalQuantity = SUM( 'Table'[Quantity] ) % Total = [TotalQuantity] / CALCULATE( [TotalQuantity] ,ALL( 'Table' ) )Hello community, greetings from Lima!.
I am using this formula proposed by you:
04_%of_total_leads =[01_total_gross_leads]
/CALCULATE([01_total_gross_leads],ALL('01_dB'))But the % is not adjusting when I filter another visual... I mean the context filtering is not adjusting accordingly... please help!Building on this - I am trying a similar approach (using your same formula) and not getting the answer I expect.
Effectively, I am building a stacked bar chart where I hope to determine % of total business in each calendar year and month (so year + month is a bar in the chart). I am only showing for our top 15 customers, so by construction, the total % should be < 100 for a given period. The total currently exceeds 100 in each period, so something is definitively wrong with the calculation (namely, the numerator is probably for data for more than just the period for the shown bar).
Do you have advice as to how to fix this? Specifically, I am connected to a company database, so I can only add measures, not calculated columns. Many thanks in advance for your help!
Yeah, I totally fubarred that DAX formula, what I was going for was:
= SUM([Quantity])/SUMX('table',ALL('table'[Quantity]))
I like you splitting it out into separate measures though, makes it cleaner and easier to understand.
That's what I get thinking I can do this stuff from memory sometimes instead of actually firing up Desktop and doing it.
Greg_Decklergreggyb I would suggest always use DIVIDE function as it doesn't return errors on zero values and at most data models is faster..Unless you have any other opinion I would love to hear it..
And you guys are super fast - and with well explained answers!!!
konstantinos, that's a great point, I always forget about DIVIDE.
Thanks so much for this, it was surprisingly hard to find a concise write-up on how to do this. I think this about the fifth time I've used one of your DAX approaches, so your contributions are very much appreciated.
