Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
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:
However, I cant make them work. Could anyone help me further?
You can try the following code:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
65 | |
50 | |
45 | |
20 | |
17 |