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.
konstantinos, that's a great point, I always forget about DIVIDE.
Greg_Deckler too much modelling on the first versions of powerpivot/ssas :smileywink:
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.
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, that article is specifically about the performance of DIVIDE() vs the construction:
IF( [Denominator] <> 0 ,[Numerator] / [Denominator] ,<what to do for [Denominator] = 0> )