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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
denxx34
Frequent Visitor

Highest ranking over all time by month?

Hello everyone,

 

can you please help me to solve the following challenge...

 

In a table I have a Group, Date and Quantity.
Then calculate a rank for the quantity.

What I also need is...
A measure that shows me which highest ranking each group has ever had in relation to past months.

So when I select... show me the year=2022 and month=4, then I see the rank for the groups and at the same time the highest rank that group has ever had.

 

iddate   qtygroup
101.01.2021 12A
205.11.2021 3B
331.12.2021 4B
401.01.2022 5A
501.02.2022 23B
631.03.2022 5C
715.04.2022 10B
815.05.2022 78B
915.01.2023 23C
1005.01.2022 23B
1106.01.2022 19C
1215.04.2022 8C
1320.04.2022 5D
1420.05.2022 1D
1520.05.2022 2B
1601.01.2021 15B
1701.04.2022 9A
1820.05.2022 2A

 

 

denxx34_0-1646923761455.png

Thx in advance!

1 ACCEPTED SOLUTION
bcdobbs
Community Champion
Community Champion

Have a look at solution in:
Example File 


Created a date table with a start of month date column (formatted as MMMM YYYY):

Date = 
    VAR EarliestYear = YEAR ( MIN ( Sales[date] ) )
    VAR LatestYear = YEAR ( MAX ( Sales[date] ) )

    RETURN
        ADDCOLUMNS (
            CALENDAR (
                DATE ( EarliestYear, 1, 1 ),
                DATE ( LatestYear, 12, 31 )
            ),
            "Month Year", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
        )

 

Created a Group dimension table:

Group = 
    DISTINCT ( Sales[group] )

 

 

Main measures:

Total Quantity = SUM ( Sales[qty] )
Selected Ranking = 
VAR Result =
    RANKX (
        ALLSELECTED ( 'Group'[group] ),
        [Total Quantity]
    )

RETURN 
    IF (
        NOT ISBLANK ( [Total Quantity] ),
        Result
    )
Highest Monthly Rank = 
    MINX (
        ALL ( 'Date'[Month Year] ),
        [Selected Ranking]
    )

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

1 REPLY 1
bcdobbs
Community Champion
Community Champion

Have a look at solution in:
Example File 


Created a date table with a start of month date column (formatted as MMMM YYYY):

Date = 
    VAR EarliestYear = YEAR ( MIN ( Sales[date] ) )
    VAR LatestYear = YEAR ( MAX ( Sales[date] ) )

    RETURN
        ADDCOLUMNS (
            CALENDAR (
                DATE ( EarliestYear, 1, 1 ),
                DATE ( LatestYear, 12, 31 )
            ),
            "Month Year", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
        )

 

Created a Group dimension table:

Group = 
    DISTINCT ( Sales[group] )

 

 

Main measures:

Total Quantity = SUM ( Sales[qty] )
Selected Ranking = 
VAR Result =
    RANKX (
        ALLSELECTED ( 'Group'[group] ),
        [Total Quantity]
    )

RETURN 
    IF (
        NOT ISBLANK ( [Total Quantity] ),
        Result
    )
Highest Monthly Rank = 
    MINX (
        ALL ( 'Date'[Month Year] ),
        [Selected Ranking]
    )

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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