Advice to optimize performance for a calculation of cumulative %

[RegionH]

Hi

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

[RegionH · Accepted solution]

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

[RegionH]

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.

[bhanu_gautam]

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

[RegionH]

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

[bhanu_gautam]

@RegionH 

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 (
SUMX(
FILTER(
ALLSELECTED('Leverandør'),
'Leverandør'[Leverandør] <= CurrentSupplier
),
[Total Forbrug]
)
)
RETURN
DIVIDE (
CumulativeForbrug,
[Forbrug ialt],
0
)