Drill down on measure on Pivot Table

[tommaso26]

Hi everyone,

I am using a Pivot Table to display some data: for example Number of Customers by Continent and by Year/Month as shown here below:

Each number is the sum of different values depending on several dimensions. I would like to drill down on the Year Month (by clicking on it) to see the values behind the sum. Something like this:

It think it should be something like drilling on the measure, but I don't know how to achieve this result.  Do you have any idea?

Thank you very much!

Tommaso

[justISO · Accepted solution]

Hi,

when you building your pivot table, into 'Dimensions' as row you place 'your country field' and as 2 columns you add 'your year field' and additionally 'your type field'. As 'Measures' you put 'number of customers' measure.
You will have aggregated view per year and if you 'expand all' you will see number of customers per type:

Other solution, if you don't have 'type', but want to see different measures when one month is selected, you can on your 'main' measure add condition under 'show column if' GetSelectedCount(your_year_field)<>1 and under additionally created measures you add GetSelectedCount(your_year_field)=1 . In this case when only one value of year is selected, you will see your measures, but not 'main' one:

And if you filter more than one year, or nothing, you will see your 'main' measure:

[tommaso26]

Hi @justISO !

 It works perfectly! 😁

Since I don't have "type" fields, I have to use the second solution that you showed and... it works!

Thank you very much for the answer,

Tommaso