Forum Discussion
Percent of Total
I cannot find the DAX formula that will give me the % of Total results. I added a calculated field (%) to the below power BI table. In that column I am trying to get it to display the results as shown in red. In this case, 541 divided by the total 1558 and 1017 divided by the total 1558. How would I write the % formula to accomplish this? Thx
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.
14 Replies
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' ) )
