Forum Discussion

claudedubois's avatar
claudedubois
Regular Visitor
3 years ago

Sorting by total spend, getting cumulative % , and categorizing

Hello everyone,    I hope all is well.   Kindly need your help with the function below. Able to do it in Excel but not on PBI.    This is the easy option to do it, the more complex one is to th...
Need Help
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi claudedubois 
Please refer to attached sample file with DAX (Measure) solution

Result = 
VAR CurrentSKU = 
    SELECTEDVALUE ( 'Table'[SKU] )
VAR T1 =
    ADDCOLUMNS ( 
        ALLSELECTED ( 'Table'[SKU] ),
        "@Spend", CALCULATE ( SUM ( 'Table'[Spend] ) )
    )
VAR TotalSpend = 
    SUMX ( T1, [@Spend] )
VAR T2 = 
    GENERATE ( 
        T1,
        VAR CumulativeSpend = 
            SUMX ( 
                FILTER ( T1, [@Spend] >= EARLIER ( [@Spend] ) ),
                [@Spend]
            )
        VAR CumulativePercentage =
            DIVIDE ( CumulativeSpend, TotalSpend )
        RETURN
            ROW ( "@Percentage", CumulativePercentage )
    )
VAR T3 = 
    FILTER ( T2, [SKU] = CurrentSKU )
VAR CurrentPercentage = 
    MAXX ( T3, [@Percentage] )
RETURN
    IF ( CurrentPercentage > 0.95, "Tail", "Non - Tail" )