top 5 and bottom 5

[dsmitha]

Hi,

Am trying to develop a dashboard which will show the top five sales and bottom five sales  detais using a column chart visual. 

I would like to add a measure instead of the filter options. Request your guidance to solve this issue

regards,

dsmitha

[amitchandak · Accepted solution]

@dsmitha , You can measures like ([Sales] is a measure)

TOP 5= Divide( CALCULATE([Sales],TOPN(5,all(Geography[City]),[Sales],DESC),VALUES(Geography[City]))

Botton 5 = Divide( CALCULATE([Sales],TOPN(5,all(Geography[City]),[Sales],DESC),VALUES(Geography[City]))

Combine = Divide( CALCULATE([Sales],TOPN(5,all(Geography[City]),[Sales],DESC),VALUES(Geography[City])) +

Divide( CALCULATE([Sales],TOPN(5,all(Geography[City]),[Sales],DESC),VALUES(Geography[City]))

Refer: https://youtu.be/QIVEFp-QiOk 

[Jihwan_Kim · Accepted solution]

Hi,

I like to suggest using TOPN DAX function with KEEPFILTERS DAX function.

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Sales measure: = 
SUM( Data[Sales] )

Top five products: = 
CALCULATE (
    [Sales measure:],
    KEEPFILTERS ( TOPN ( 5, ALL ( Data ), [Sales measure:], DESC ) )
)

Bottom five products: = 
CALCULATE (
    [Sales measure:],
    KEEPFILTERS ( TOPN ( 5, ALL ( Data ), [Sales measure:], ASC ) )
)

[ribisht17 · Accepted solution]

@dsmitha 

Using RANKX > Top and Bottom 10 Products by Sales Using RANKX in Power BI - YouTube

Using the Same Visual > Solved: How to display Top 5 and Bottom 5 in the single ch... - Microsoft Power BI Community

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users 

[dsmitha]

Hi ribisht17,

thank you very much 

regards,

dsmitha

PREVIEW

[Jihwan_Kim · Accepted solution]

Hi,

I like to suggest using TOPN DAX function with KEEPFILTERS DAX function.

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Sales measure: = 
SUM( Data[Sales] )

Top five products: = 
CALCULATE (
    [Sales measure:],
    KEEPFILTERS ( TOPN ( 5, ALL ( Data ), [Sales measure:], DESC ) )
)

Bottom five products: = 
CALCULATE (
    [Sales measure:],
    KEEPFILTERS ( TOPN ( 5, ALL ( Data ), [Sales measure:], ASC ) )
)

[dsmitha]

Hi Jihwan_Kim,

Thank you very much for your guidance, this helped a lot 

regards,

dsmitha

[amitchandak · Accepted solution]

@dsmitha , You can measures like ([Sales] is a measure)

TOP 5= Divide( CALCULATE([Sales],TOPN(5,all(Geography[City]),[Sales],DESC),VALUES(Geography[City]))

Botton 5 = Divide( CALCULATE([Sales],TOPN(5,all(Geography[City]),[Sales],DESC),VALUES(Geography[City]))

Combine = Divide( CALCULATE([Sales],TOPN(5,all(Geography[City]),[Sales],DESC),VALUES(Geography[City])) +

Divide( CALCULATE([Sales],TOPN(5,all(Geography[City]),[Sales],DESC),VALUES(Geography[City]))

Refer: https://youtu.be/QIVEFp-QiOk 

[dsmitha]

Hi amitchandak,

Thanks a lot for your timely help

regards,

dsmitha

PREVIEW