Custom Calculation for % Share at each Hierarchy Level

[ltekchandani]

Hi,

I have data like this:

Application	Show	id
Netflix	The Office	1
Netflix	The Office	2
Netflix	The Office	3
Netflix	The Office	4
Netflix	Friends	3
Netflix	Friends	4
Netflix	Friends	5
Amazon Prime	Dhamaal	2
Amazon Prime	Dhamaal	4
Amazon Prime	Dhamaal	5
Amazon Prime	Hindi	4
Amazon Prime	Hindi	5

And I have to display this table in PowerBI

Application	Show	Unique ID Count	% Total ID Share	% of Application ID Share
Netflix	The Office	4	80%	100%
Netflix	Friends	3	60%	75%
Amazon Prime	Dhamaal	3	60%	100%
Amazon Prime	Hindi	2	40%	67%

Here are the definitions for Calculated Columns:

% Total ID Share = How many % of Total TVs also watch XYZ show

% Application ID Share = How many % of TVs watch Netflix also watch XYZ show

I want to get a Measure formula for "% Application ID Share" column. I have tried using "Show as Percent of Grand Total", "Show as Percent of Row Total" but that does not work for me. Can somebody help?

Thank you!

[lbendlin]

I think your expected result is slightly off. Netflix serves all five IDs.

ID Share = DIVIDE(DISTINCTCOUNT('Table'[id]),CALCULATE(DISTINCTCOUNT('Table'[id]),ALL('Table')),0)

App Share = DIVIDE(DISTINCTCOUNT('Table'[id]),CALCULATE(DISTINCTCOUNT('Table'[id]),REMOVEFILTERS('Table'[Show])),0)