Forum Discussion

dparkinson's avatar
dparkinson
Advocate I
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable

    dparkinson,

    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