Forum Discussion
Dynamic TopN or Amount and Column Filtering
I have a situation where I have a revenue value for multiple departments (or service areas). Rather than create a separate tab for each department, I thought it would be better to have a dynamic report that would let you select departments.
We might want to see the top 10 items by value for some departments and any items over a certain amount for others. The information will also be filtered by date (I mention that as that caused some issues when I was messing around with the TOPN function).
As an example the columns and the items I'd like to see are:
Dept 1 Revenue - Top 10 Items
Dept 2 Revenue - Top 10 Items
Dept 3 Revenue - >=15000
Dept 4 Revenue - >= 20000
etc.
There are around 9 departments.
The result would be a table that looked like:
Customer Name, Work Description, Work Date, Dept 1 Revenue
Then when a slicer is selected, it would change to
Customer Name, Work Description, Work Date, Dept 3 Revenue
Where for the first one, it's showing the top ten entries by value for department 1 and the second is showing anything over 15000.
I hope that makse sense. Any thoughts greatly appreciated.
Many thanks.
1 Reply
- AnonymousNot applicable
In your scenario, you can create measures about Dept1 TopN revenue, Dept2 TopN revenue, Dept 3 Over 15000 revenue and so on, then create a new table containing the measure names. This way, it will allow you to use slicers to choose different measures to display in a table without having to build multiple tables. For more details, please review this similar blog: http://breaking-bi.blogspot.sg/2016/06/power-bipowerpivot-using-slicers-to.html .
Besides, you can use RANKX function to calculate the TopN revenue measures.
If you have any questions about how to create DAX in your scenario, please share sample data of your table.Regards,
Lydia Zhang