Forum Discussion

scootzinc's avatar
scootzinc
Frequent Visitor
3 years ago
Solved

Need help with dynamic ranking using dax

vendor group year invoice # spend 1002 A 2023 10001  $  3,275,404.00 1002 A 2023 10002  $  3,919,979.00 1002 B 2022 10003  $  3,320,...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Spend: =
SUM ( Data[spend] )

     

    Rank: =
RANKX ( ALL ( vendor[vendor] ), [Spend:],, DESC )

     

    spend cumulate classify =
VAR _allspend =
    CALCULATE ( [Spend:], ALL ( vendor[vendor] ) )
VAR _cumulatespend =
    CALCULATE (
        [Spend:],
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ADDCOLUMNS ( ALL ( vendor[vendor] ), "@spend", [Spend:] ),
            ORDERBY ( [@spend], DESC )
        )
    )
VAR _percentage =
    DIVIDE ( _cumulatespend, _allspend )
RETURN
    IF (
        HASONEVALUE ( vendor[vendor] ),
        IF ( _percentage <= 0.8, "top80%", "bottom20%" )
    )

     

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

Please check the below picture and the attached pbix file.

 

 

 

Spend: =
SUM ( Data[spend] )

 

Rank: =
RANKX ( ALL ( vendor[vendor] ), [Spend:],, DESC )

 

spend cumulate classify =
VAR _allspend =
    CALCULATE ( [Spend:], ALL ( vendor[vendor] ) )
VAR _cumulatespend =
    CALCULATE (
        [Spend:],
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ADDCOLUMNS ( ALL ( vendor[vendor] ), "@spend", [Spend:] ),
            ORDERBY ( [@spend], DESC )
        )
    )
VAR _percentage =
    DIVIDE ( _cumulatespend, _allspend )
RETURN
    IF (
        HASONEVALUE ( vendor[vendor] ),
        IF ( _percentage <= 0.8, "top80%", "bottom20%" )
    )

 

 

 

scootzinc's avatar
scootzinc
Frequent Visitor
3 years ago

this appears to be working. the ranking is working. when adding in the classify measure it runs out of memory and fails. i have a data set of millions of rows. is there a way to move this into a sub table or to solve the memory issue? technically i only need to classify it for invoices for the present year and can exclude all vendors with $0 spend.