Need Help or Workaround With Measures

[matthew_hampton]

Hello - 

I am going to attempt to explain the issue I am having as best I can with both screen shots.

I have multiple tables related to AccountsMasterList but for purposes of this question, I am focusing on the ViewershipData table.

Viewship data contains some usage data and for purposes of this, I want to look at hours watched by Account Number by Programmer and by Channel Name. I have this data for the past 365 days, so there are ~70M rows in this table.

What I am looking to do is the following:

Select a programmer via a slicer and look by Account Number at HoursWatched for that programmer as a percentage of their total hours watched. I have been able to acomplish this with a measure that renders correctly.

% of Total Viewing = DIVIDE(CALCULATE(SUM(ViewershipData[HoursWatched])),CALCULATE(SUM(ViewershipData[HoursWatched]),ALL(ViewershipData[Programmer])),0)

So here is where I am stuck with what I want to do:

1. I want users to be able to select a programmer and select a range of the % of Total Viewing and view information about those accounts. Example: A user wants to look at all Account Numbers where usage of the selected programmer is between 6% and 7%.

2. Then (preferably through a drill through but would be okay with any working option), I want to then be able to look at the channels for the selected programmer and see of those selected accounts what are the Hours Viewed per channel as a percent of the programmer grand total. I created a working measure similar to the one above for the channel values and it renders properly when I select a programmer.

% of Total Viewing (Channel) = DIVIDE(CALCULATE(SUM(ViewershipData[HoursWatched])),CALCULATE(SUM(ViewershipData[HoursWatched]),ALL(ViewershipData[Channel)),0)

I know that due to how measure work and render that a measure cannot be used on a slicer. I also know that you can do a visual level filter to acomplish getting the accounts list with the first measure manually but I have a page with 9-10 visuals that all will need adjusted. 

I hope this makes sense. If you need a working file, I can try to provide a slimmed down version withouth any proprietary information in it.

Thanks in advance for any help and suggestions.

Matt

[v-chuncz-msft · Accepted solution]

@matthew_hampton,

You may take a look at the following posts.

• https://community.powerbi.com/t5/Desktop/Use-a-date-slicer-to-filter-on-a-period-instead-of-a-single...
• https://community.powerbi.com/t5/Desktop/Create-a-bar-graph-with-measures-as-axis/td-p/455900