Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
scootzinc
Frequent Visitor

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,651.00

1002

B

2023

10004

 $  2,385,653.00

1002

B

2023

10005

 $  2,158,321.00

1002

B

2023

10006

 $  4,246,098.00

1002

B

2022

10007

 $  4,693,622.00

1005

A

2023

10008

 $  1,648,983.00

1005

A

2023

10009

 $  2,403,157.00

1005

A

2022

10010

 $      444,527.00

1005

A

2023

10011

 $  1,075,092.00

1005

C

2023

10012

 $  4,355,503.00

1005

C

2023

10013

 $      817,127.00

1005

C

2022

10014

 $      797,043.00

1005

B

2023

10015

 $      304,520.00

1005

B

2023

10016

 $      471,860.00

1005

B

2022

10017

 $  1,823,371.00

2007

C

2023

10018

 $  2,242,889.00

2007

C

2023

10019

 $  1,974,326.00

2007

C

2022

10020

 $  1,698,917.00

2007

C

2023

10021

 $  1,578,562.00

2007

C

2023

10022

 $      142,124.00

2007

C

2023

10023

 $  1,233,712.00

2007

C

2022

10024

 $  3,520,975.00

2007

C

2022

10025

 $  1,833,172.00

2007

C

2023

10026

 $  2,468,742.00

2007

C

2022

10027

 $  2,074,428.00

2007

C

2023

10028

 $  2,011,873.00

2007

C

2023

10029

 $  3,870,067.00

5006

A

2023

10030

 $      686,474.00

4007

A

2022

10031

 $      307,452.00

2027

A

2022

10032

 $      655,701.00

2027

C

2023

10033

 $      557,174.00

2027

C

2022

10034

 $        63,068.00

2027

C

2023

10035

 $      676,519.00

2027

B

2023

10036

 $      325,517.00

2027

B

2023

10037

 $      437,127.00

4007

A

2022

10038

 $      535,736.00

4007

A

2022

10039

 $      351,313.00

4007

A

2023

10040

 $      547,303.00

5006

C

2022

10041

 $      754,842.00

5006

C

2023

10042

 $      271,665.00

5006

C

2023

10043

 $      359,705.00

5006

B

2023

10044

 $        75,995.00

5006

B

2022

10045

 $      198,562.00

5006

C

2022

10046

 $      790,911.00

5006

B

2023

10047

 $      592,415.00

2027

B

2022

10048

 $      591,265.00

4007

A

2023

10049

 $      516,936.00


Using the above data set:

I want to get a measure that tells me the net spend for each vendor, for each group, for the year 2023. I then want to rank them by net spend. And then I want to classify them into two groups. Vendors that make up the top 80% of spend, and vendors that make up the bottom 20% of spend, based on net 2023 spend. 

 

Any help on how to do this so that it calculates dyanmically and I can choose whether or not I want to filter by group?

 

NOTE: I have millions of rows of invoices. 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1680232794538.png

 

Jihwan_Kim_1-1680233601698.png

 

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%" )
    )

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1680232794538.png

 

Jihwan_Kim_1-1680233601698.png

 

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%" )
    )

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors