cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

 

PowerBI%OfSales.jpg

14 REPLIES 14
Super User
Super User

Re: Percent of Total

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
greggyb New Contributor
New Contributor

Re: 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.

Super User
Super User

Re: Percent of Total

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

konstantinos Senior Member
Senior Member

Re: Percent of Total

@Greg_Deckler@greggyb 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 Ioannou
Super User
Super User

Re: Percent of Total

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

konstantinos Senior Member
Senior Member

Re: Percent of Total

@Greg_Deckler  too much modelling on the first versions of powerpivot/ssas Smiley Wink

Konstantinos Ioannou
greggyb New Contributor
New Contributor

Re: Percent of Total

@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.

konstantinos Senior Member
Senior Member

Re: Percent of Total

@greggyb No I haven't profiled DIVIDE function but I thought so based on SQLBI article which I trust..

https://www.sqlbi.com/articles/divide-performance/

 

I think the statement   that use of Divide ( or similar ) instead of operator suggests that a measure or data model in not well known or understadable to the author. Sometimes is a matter of style  ( like AND() instead of && or the opposite ) or other times the sources are not SQL/DB with data validation but simple excel files that users add manually data prone to errors.

 

And of course a suggestion on a formula don't implement anything about my (either way limited) knowledge of DAX.

Konstantinos Ioannou
greggyb New Contributor
New Contributor

Re: Percent of Total

@konstantinos, that article is specifically about the performance of DIVIDE() vs the construction:

IF(
    [Denominator] <> 0
    ,[Numerator] / [Denominator]
    ,<what to do for [Denominator] = 0>
)

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 401 members 3,703 guests
Please welcome our newest community members: