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