topic Re: Waterfall chart with Top N / Bottom N sales based on slicer in DAX Commands and Tips

Waterfall chart with Top N / Bottom N sales based on slicer

PowerBIoverExcl — Wed, 30 Nov 2022 14:45:00 GMT

Dear PowerBI Community,

Despite having checked multiple threads on applicable DAX formula’s I can’t seem to find the correct solution for what I’m searching for. I would greatly appreciate it if someone could help me.

For a data source, I have a single dataset of customer names, time periods and sales values. I want to setup a waterfall chart whereby between the totals, I want to show the top-5 gainers in sales, top-5 declines in sales and have the sales change of all other customers grouped together in ‘Others’. I verified that this can only be achieved using DAX formulas (i.e. not with just visualisation options).

The thing that complicates this is that I have a slicer whereby the user can select any (2 or more) time periods, so the DAX formula needs to be dynamic to accommodate any combination of time periods.

Would appreciate any DAX help.

Re: Waterfall chart with Top N / Bottom N sales based on slicer

Anonymous — Thu, 01 Dec 2022 06:00:26 GMT

Hi PowerBIoverExcl ,

You can create a measure, measures can change dynamically with filtering

Measure = SUM('Table'[Sales])

Then create the Waterfall chart and put the following fields in visual filter, ‘shown items’ select Top 6 , and put the measure to the ‘By value’, then click ‘Apply filter’

Then change the “Breakdown” in format, input “5”

The final output

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

Re: Waterfall chart with Top N / Bottom N sales based on slicer

PowerBIoverExcl — Thu, 01 Dec 2022 17:22:24 GMT

Hi Yolo Zhu,

Thanks for the response, but this method does not produce the result I’m looking for. I want to show the largest 5 customers individually, as well as the bottom-5 individually by sales, and have the effects of all other customer grouped in a subtotal ‘Others’. Furthermore, applying the filter to the Top N also affects the results of the totals in the waterfall bridge, whereas I would like the totals to include the total of all customers for the selected time periods.

I created the following DAX measures to help me sort which impacts to show separately:

Total_Revenue = SUM(Combined[Revenue])
Total_GP = SUM(Combined[Gross profit])
Concentration_Rank = RANKX(ALL(Combined[Customer]),SUMX(Combined,Combined[Revenue]))
Concentration_Display_Names = IF(Combined[Concentration_Rank]>10,"Other",0)

However, I cant make them work. Could anyone help me further?

Re: Waterfall chart with Top N / Bottom N sales based on slicer

Anonymous — Fri, 02 Dec 2022 09:16:05 GMT

Hi PowerBIoverExcl

You can try the following code:

SUM_RE_Bytime&Customer =

SUMX(FILTER(ALL('Table'),'Table'[Customer]=SELECTEDVALUE('Table'[Customer])&&'Table'[Time period]=SELECTEDVALUE('Table'[Time period])),[Revenue])

Type = var a= RANKX(FILTER(ALL('Table'),'Table'[Time period]=SELECTEDVALUE('Table'[Time period])),[SUM_RE_Bytime&Customer]) return if(a>=10,"other",0)

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}