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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.


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.


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors