topic Advice to optimize performance for a calculation of cumulative % in DAX Commands and Tips

Advice to optimize performance for a calculation of cumulative %

RegionH — Tue, 04 Mar 2025 13:40:53 GMT

I'm having a datamodel with a fact table called 'Ordre- og Fakturalinjer', which contains over 15 million rows of order and invoice lines. One of the columns is [Leverandør ID], which is connected to the dimension table called 'Leverandør', which contain 16.000 unique suppliers.

I have created these measures to calculate a Akkumuleret Forbrug % (Cumulative %):

Total Forbrug = SUM ( 'Ordre- og Fakturalinjer'[Forbrug (DKK)] )

Total Forbrug Rank = IF ( ISINSCOPE ( 'Leverandør'[Leverandør] ), RANKX( ALLSELECTED ( 'Leverandør'[Leverandør] ), [Total Forbrug]) )

Total Forbrug GT% = DIVIDE ( [Total Forbrug], [Forbrug ialt], 0 )

Akkumuleret Forbrug % = divide ( [Akkumuleret Forbrug], [Forbrug ialt], 0 )

Then I use following columns in a table visual:
'Leverandør'[Leverandør]
[Total Forbrug]
[Total Forbrug Rank]

[Total Forbrug GT%]
[Akkumuleret Forbrug %]

When I trace the performance of the table in DAX Studio it takes almost 167.000 ms. The problem lies in the measure [Akkumuleret Forbrug %].

Any advice to optimize the existing measure or do it in another way to get faster load time for the visual table.

Best regards
Morten

Re: Advice to optimize performance for a calculation of cumulative %

bhanu_gautam — Tue, 04 Mar 2025 13:59:57 GMT

RegionH Try using

DAX
Total Forbrug = SUM ( 'Ordre- og Fakturalinjer'[Forbrug (DKK)] )

Total Forbrug Rank =
VAR CurrentSupplier = SELECTEDVALUE('Leverandør'[Leverandør])
RETURN
IF (
ISINSCOPE ( 'Leverandør'[Leverandør] ),
RANKX(
ALLSELECTED ( 'Leverandør'[Leverandør] ),
[Total Forbrug],
,
DESC,
DENSE
)
)

Total Forbrug GT% =
DIVIDE (
[Total Forbrug],
[Forbrug ialt],
0
)

Akkumuleret Forbrug % =
VAR CurrentSupplier = SELECTEDVALUE('Leverandør'[Leverandør])
VAR CumulativeForbrug =
CALCULATE (
[Total Forbrug],
FILTER (
ALLSELECTED('Leverandør'[Leverandør]),
[Total Forbrug Rank] <= RANKX(ALLSELECTED('Leverandør'[Leverandør]), [Total Forbrug])
)
)
RETURN
DIVIDE (
CumulativeForbrug,
[Forbrug ialt],
0
)

Re: Advice to optimize performance for a calculation of cumulative %

RegionH — Tue, 04 Mar 2025 14:19:11 GMT

Thank you for your respons. It is definetely very fast, but unfortunately it is not showing the correct cummulative percentage. It shows 1 on each row. I would expect on row one the percentage is 11,2% and the percentage on row two is 19,1%.

Best regards
Morten

Re: Advice to optimize performance for a calculation of cumulative %

bhanu_gautam — Tue, 04 Mar 2025 14:54:53 GMT

RegionH

dax
Total Forbrug = SUM ( 'Ordre- og Fakturalinjer'[Forbrug (DKK)] )

Total Forbrug GT% =
DIVIDE (
[Total Forbrug],
[Forbrug ialt],
0
)

Akkumuleret Forbrug % =
VAR CurrentSupplier = SELECTEDVALUE('Leverandør'[Leverandør])
VAR CumulativeForbrug =
CALCULATE (
SUMX(
FILTER(
ALLSELECTED('Leverandør'),
'Leverandør'[Leverandør] <= CurrentSupplier
),
[Total Forbrug]
)
)
RETURN
DIVIDE (
CumulativeForbrug,
[Forbrug ialt],
0
)

Re: Advice to optimize performance for a calculation of cumulative %

RegionH — Tue, 04 Mar 2025 15:32:18 GMT

The measure is definitely faster with 90.000 ms. But it's showing the incorrect cumulative percentages, because you use SELECTEDVALUE() to compare suppliers in your filter. This approach does not work correctly in an accumulated measure because you are trying to compare text values directly across multiple rows, and SELECTEDVALUE() returns only one value.

Re: Advice to optimize performance for a calculation of cumulative %

RegionH — Wed, 05 Mar 2025 11:48:02 GMT

I ended up with this DAX code, which is executed in 4.522 ms, which is acceptable for me.

Running Total % = VAR _SpendTotal = CALCULATE ( [Total Forbrug], ALLSELECTED ( 'Leverandør'[Leverandør] ) ) VAR _FilteredTable = CALCULATETABLE ( ALLSELECTED ( 'Leverandør'[Leverandør] ), 'Ordre- og Fakturalinjer'[Forbrug (DKK)] > 0 ) VAR _NewTable = ADDCOLUMNS ( _FilteredTable, "@spendtotal", [Total Forbrug] ) VAR _CumulateTable = ADDCOLUMNS ( _NewTable, "@cumulatesum", SUMX ( FILTER ( _NewTable, [@spendtotal] >= EARLIER ( [@spendtotal] ) ), [@spendtotal] ), "@total", _SpendTotal ) VAR _Result = MAXX ( FILTER ( ADDCOLUMNS ( _CumulateTable, "@result", DIVIDE ( [@cumulatesum], [@total] ) ), 'Leverandør'[Leverandør] = MAX ( 'Leverandør'[Leverandør] ) ), [@result] ) RETURN IF ( HASONEVALUE ( 'Leverandør'[Leverandør] ), _Result, BLANK() )