How to show top 5 and lowest 5 in pie chart dynamically with dates and different brands

[Niels_T]

Hello,

I have multiple pie charts in my reports with sales, orders, add to carts, etc. based on products.

I would like to make a top 5 and the lowest 5 with a pie chart as we have many different products.

In this report we also use dates and different brands so it also has to  be dynamic in a way that whenever I select a date and particular brand the data changes according to those selections.

 I tried different ways like looking at the filters on the visual, but I can't find anything about TopN or something.

- N

[amitchandak · Accepted solution]

@Niels_T , a measure like

Top and bottom= CALCULATE([Sales],TOPN(5,all(product[brand]),[Sales],DESC),VALUES(product[brand])) + CALCULATE([Sales],TOPN(5,all(product[brand]),[Sales],asc),VALUES(product[brand]))

or measures like

Rank1 = RANKX(all(product[brand]),[Sales],,desc)

Rank2 = RANKX(all(product[brand]),[Sales],,desc)

Rank Top Bottom = sumx(VALUES(product[brand]),if([Rank1]<=5 || [Rank2] <=5,[Sales],BLANK()) )

[timg · Accepted solution]

Hi Niels,

Do you want one piechart to contain either a top 5 or bottom 5? Or did you mean it would have to contain both at the same time? If you want to visualize just the top or bottom 5 per visual there is a top N option in de filter pane. see reference image

example top N

Regards,

Tim

[amitchandak · Accepted solution]

@Niels_T , a measure like

Top and bottom= CALCULATE([Sales],TOPN(5,all(product[brand]),[Sales],DESC),VALUES(product[brand])) + CALCULATE([Sales],TOPN(5,all(product[brand]),[Sales],asc),VALUES(product[brand]))

or measures like

Rank1 = RANKX(all(product[brand]),[Sales],,desc)

Rank2 = RANKX(all(product[brand]),[Sales],,desc)

Rank Top Bottom = sumx(VALUES(product[brand]),if([Rank1]<=5 || [Rank2] <=5,[Sales],BLANK()) )