Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

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 sh...
  • greggyb's avatar
    greggyb
    10 years ago

    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's avatar
greggyb
Resident Rockstar
10 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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' )
    )

 

  • cabc_xlorla's avatar
    cabc_xlorla
    Advocate IV
    7 years ago

    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!
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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!