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
MrMP
Helper III
Helper III

Product ranking

Hi,

 

Is there a way when user selects one product in upper chart for line chart below to plot him what was product rank for each day?

For example, if total of 10 products were sold that day, product was on 2nd place ---> 2/10

 

MrMP_4-1713430815218.png

Dataset is quite simple. Date | Product | Sales

 

Thank you!

 

1 ACCEPTED SOLUTION
v-yaningy-msft
Community Support
Community Support

Hi, @MrMP 

First, thanks for @amitchandak  help. If you have not solve the problem, you can try the following method.

vyaningymsft_0-1713492062993.pngvyaningymsft_1-1713492090393.png

DAX:

Rank = 
RANKX (
    FILTER (
        'Table',
        'Table'[Date] = EARLIER ( 'Table'[Date] )
    ),
    'Table'[Sales],
    ,
    DESC,
    Dense
)
MaxRank = 
VAR _maxRank = CALCULATE(MAX('Table'[Rank]))
RETURN
_maxRank

RANK BY Product =
IF (
    CALCULATE ( SUM ( 'Table'[Rank] ) ) > [MaxRank],
    "Please select one Product",
    [MaxRank]
)

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

Not sure of what result you want.  Share data in a format that can be pasted in an MS Excel file.  Show the expected result clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yaningy-msft
Community Support
Community Support

Hi, @MrMP 

First, thanks for @amitchandak  help. If you have not solve the problem, you can try the following method.

vyaningymsft_0-1713492062993.pngvyaningymsft_1-1713492090393.png

DAX:

Rank = 
RANKX (
    FILTER (
        'Table',
        'Table'[Date] = EARLIER ( 'Table'[Date] )
    ),
    'Table'[Sales],
    ,
    DESC,
    Dense
)
MaxRank = 
VAR _maxRank = CALCULATE(MAX('Table'[Rank]))
RETURN
_maxRank

RANK BY Product =
IF (
    CALCULATE ( SUM ( 'Table'[Rank] ) ) > [MaxRank],
    "Please select one Product",
    [MaxRank]
)

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

amitchandak
Super User
Super User

@MrMP , if you create a rank measure it will get distributed based on date in second visual and will give date of filtered product 

 

rankx(allselected(Table[Product]), [Sales measure])

 

or new rank function

 

rank(DENSE,ALLSELECTED(Table[Product], Table[Date]),ORDERBY([Sales],DESC),, partitionBy(Table[Date]) )

 

Power BI - New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo

Thank you for help. Bottom chart should be trend over time. With that measure, it gives rank 1 for every calendar date when I select product.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors