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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

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.

konstantinos's avatar
konstantinos
Icon for Memorable Member rankMemorable Member
10 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago

    konstantinos, that's a great point, I always forget about DIVIDE.

    • konstantinos's avatar
      konstantinos
      Icon for Memorable Member rankMemorable Member
      10 years ago

      Greg_Deckler  too much modelling on the first versions of powerpivot/ssas :smileywink:

      • greggyb's avatar
        greggyb
        Icon for Resident Rockstar rankResident Rockstar
        10 years ago

        konstantinos, have you profiled DIVIDE() to be faster than the division operator? This goes against msot of what I've seen. I typically use DIVIDE() when I'm unsure about someone's data. The denominator in this example is basically guaranteed to return data unless the model is empty, so I see no reason to use DIVIDE().

         

        I try to use the minimally powerful abstractions wherever possible, and let my functions act as indicators about my knowledge of the data.

         

        E.g. TOPN() and SAMPLE() will return the same number of rows if there are no ties. I would use TOPN() if I knew a tie shouldn't be possible, and let the measure blow up if that assumption is wrong and it depends on a strict number of rows returned (e.g. for TOPN(1, ...) expecting implicit conversion to a scalar value). Using SAMPLE() would be "safer", but implies I expect ties.

         

        Similarly, DIVIDE() indicates that I either do not understand the data / measure well, or that I expect denominators = 0 in normal use.