Drill down in created measure, how to?

ShaunH

New Member
Joined
Jan 30, 2020
Messages
27
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi there
I want to use a measure to count the number of times certain words appear in a column and based on that, be able to drill down and look at those records in a new sheet when double clicking on the value.
I am able to create explicit measures in the data model, but am not able to drill down into the values shown in the pivot table, as the values are filtered in the measures. I have tried implicit measures too without much success.

I also use slicers to filter further as I have YTD data and need to select which month I would like to see the data for, or at times the day or select a user or devision.
I have tried a couple ways and get the same results from all the measures.
Measure1:=CALCULATE(COUNT(Table[Color]),FILTER(ALL(Table[Color]),Table[Color] = "Red" || Table[Color] = "Green"))
Measure2:=CALCULATE(COUNTA(Table[Color]),FILTER(ALL(Table[Color]),Table[Color] = "Red" || Table[Color] = "Green"))
Measure3:=CALCULATE(COUNTROWS(Table),FILTER(Table,Table[Color] = "Red" || Table[Color] = "Green"))
Measure4:=CALCULATE(COUNT(Table[Color),FILTER(Table,Table[Color] = "Red" || Table[Color] = "Green"))
Measure5:=CALCULATE(COUNTA(Table[Color),FILTER(Table,Table[Color] = "Red" || Table[Color] = "Green"))

Another measure
Measure6:=CALCULATE(DISTINCTCOUNT(Table1[UserID]))
Measure7:=DISTINCTCOUNT(Table1[CustomerName])

The same value is shown for the calculations, no matter what derivatives I've tried.

My data model currently has over 500k lines and many columns. There are various measures for different things. The slicers affect all of them.
How does one get to drill down using filters in measures, that are also affected by slicers? I don't want to see the whole 1000 rows only, of the dataset, only the values in the measure.

This is highly annoying when you have data and some items need to be clustered together, and specific info is not view-able.

I have not found a solution on how to do this.
Anyone that can help me please?
Thank you
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

ShaunH

New Member
Joined
Jan 30, 2020
Messages
27
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I realize I left some brackets out in the above, but it seems the drill down is not available in measures, only pivot tables with implicit measures where slicers or filters are applied directly to the whole pivot. I think this is a problem. I can't use slicers and pivot filters when calculating values.
My spreadsheet measures activities and the times these were performed. There are many types of activities and they are all recorded. Measures count these activities, or sometimes some activities are grouped by filters as a similar type of activity.
So when I double-click the measure, I want to see those activities that were defined for the person, not all their activities.

If anyone does have a solution, please, I would appreciate the help.
Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,130,176
Messages
5,640,611
Members
417,157
Latest member
nirmalp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top