topic Re: TOP 10 And Bottom 10 in a single Bar Chart in DAX Commands and Tips

TOP 10 And Bottom 10 in a single Bar Chart

VagelisVlachos — Thu, 15 Dec 2022 00:17:28 GMT

Hello.

Does anyone know what i have to do to saw in 1 sinlge bar chart the top 10 and the bottom 10 customers by revenue measure?

Thank you.

Re: TOP 10 And Bottom 10 in a single Bar Chart

ppm1 — Thu, 15 Dec 2022 01:13:51 GMT

Here is an example of how to do this. In my case, I only had 6 products, so did Top/Bottom 2 only, but the pattern is the same. This is a table, but if you use the adapted measures in a bar chart, only the top/bottom will show.

Top Bottom 2 = VAR Top2 = TOPN ( 2, ALLSELECTED ( 'Product'[Product] ), [Total Sales], DESC ) VAR Bottom2 = TOPN ( 2, ALLSELECTED ( 'Product'[Product] ), [Total Sales], ASC ) RETURN CALCULATE ( [Total Sales], KEEPFILTERS ( UNION ( Top2, Bottom2 ) ) )

Pat

Re: TOP 10 And Bottom 10 in a single Bar Chart

VagelisVlachos — Thu, 15 Dec 2022 09:31:44 GMT

Hello.

Thanks for the reply but this not work properly. In the visual i dont any data for the bottom "10". I ve got only for the top "10". i have tried it in a table and in bar chart and nothing.

Top N Sum Sales =

CALCULATE (

[Revenue],

KEEPFILTERS ( TOPN ( 10, ALLSELECTED ( 'DimCustomer'[Name] ), [Revenue] ) )

)

i used this for top 10 but i can't understand what i have to do for the bottom "10"

thank you

Re: TOP 10 And Bottom 10 in a single Bar Chart

Tommyvhod — Thu, 15 Dec 2022 09:35:52 GMT

I think the easiest way is to summarize the the revenues, create a list by customer, and add a basic filter ( top 10) on the visualization ( right side where the visualizations are), and a new list with bottom 10

Re: TOP 10 And Bottom 10 in a single Bar Chart

VagelisVlachos — Thu, 15 Dec 2022 09:38:40 GMT

Yes but you will have 2 different visual.I need to have 1 single visual with the top 10 and the bottom 10.Your way has to be with 2 different.

thank you

Re: TOP 10 And Bottom 10 in a single Bar Chart

ppm1 — Thu, 15 Dec 2022 13:44:05 GMT

You didn't use the whole expression from the example. Please try this measure.

Top N Sum Sales = VAR Top10 = TOPN ( 10, ALLSELECTED ( 'DimCustomer'[Name] ), [Revenue], DESC ) VAR Bottom10 = TOPN ( 10, ALLSELECTED ( 'DimCustomer'[Name] ), [Revenue], ASC ) RETURN CALCULATE ( [Revenue], KEEPFILTERS ( UNION ( Top10, Bottom10 ) ) )

Pat

Re: TOP 10 And Bottom 10 in a single Bar Chart

VagelisVlachos — Thu, 15 Dec 2022 16:24:51 GMT

I used the whole expression and didnt work for the bottom 10.

Re: TOP 10 And Bottom 10 in a single Bar Chart

VagelisVlachos — Thu, 29 Dec 2022 10:06:06 GMT

Top/Bottom =

VAR top10 =

RANKX ( ALL ( DimCustomer[Name] ), [Gross Profit],, ASC )

VAR bot10 =

RANKX ( ALL ( DimCustomer[Name] ), [Gross Profit],, DESC )

RETURN

IF ( top10 <= 10 || bot10 <= 10, [Gross Profit], BLANK () )