cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to display Top 5 and Bottom 5 in the single chart?

Hi,

 

Anyone know how to do that?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Anonymous,

 

You can use below calculate column who ranking by two columns and mark flag based on ranking amount.

Category =
VAR _rank =
    COUNTROWS (
        FILTER (
            Sheet1,
            ISONORAFTER (
                    Sheet1[Revenue], EARLIER ( Sheet1[Revenue] ), ASC,
                    Sheet1[Sales Volume], EARLIER ( Sheet1[Sales Volume] ), ASC
            )
        )
    )
RETURN
    IF (
        _rank <= 5,
        "Top 5",
        IF ( _rank >= MAXX ( Sheet1, [SN] ) - 5, "Bottom 5", "Normal" )
    )

23.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

7 REPLIES 7
Helper II
Helper II

Hi Shiwen,

 

Can you please elaborate what do you mean by Top 5 and Bottom 5??

 

In general, this can be handled using a DAX query or M language which unions Top 5 and Bottom 5 attributes/measures.

 

Regards,

Hari T

Anonymous
Not applicable

Hi Hari,

 

I need to show the top-5 product by sales revenue and bottom-5 product by sales volume using a scatter chart with x-axis showing sales volume and y-axis showing revenue. I tried using the top-n filter but only 1 filter can be used. How will I be able to achieve this with DAX query or M language?

 

Thanks

HI @Anonymous,

 

Please provide a pbix file with sample data and expected result to help clarify your requirement and test to coding formula.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi Xiaoxin Sheng,

 

Please download this link for the sample data as well as the expected output.

https://www.dropbox.com/s/9zl9eaozer0pyq0/Sample%20data.xlsx?dl=0

 

The chart should display the products color only green and red.

 

Need some help from you guys on how to show the data correctly.

Hi @Anonymous,

 

You can use below calculate column who ranking by two columns and mark flag based on ranking amount.

Category =
VAR _rank =
    COUNTROWS (
        FILTER (
            Sheet1,
            ISONORAFTER (
                    Sheet1[Revenue], EARLIER ( Sheet1[Revenue] ), ASC,
                    Sheet1[Sales Volume], EARLIER ( Sheet1[Sales Volume] ), ASC
            )
        )
    )
RETURN
    IF (
        _rank <= 5,
        "Top 5",
        IF ( _rank >= MAXX ( Sheet1, [SN] ) - 5, "Bottom 5", "Normal" )
    )

23.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Can you please share the sample data used in this example. I'm not able to undestand the column [SN]

Sheing - I would be interested to know what represents 'SN' in the table?

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors