Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Custom Calculation for % Share at each Hierarchy Level

Hi,

I have data like this:

 

ApplicationShowid
NetflixThe Office1
NetflixThe Office2
NetflixThe Office3
NetflixThe Office4
NetflixFriends3
NetflixFriends4
NetflixFriends5
Amazon PrimeDhamaal2
Amazon PrimeDhamaal4
Amazon PrimeDhamaal5
Amazon PrimeHindi4
Amazon PrimeHindi5

 

And I have to display this table in PowerBI

 

ApplicationShowUnique ID Count% Total ID Share% of Application ID Share
NetflixThe Office480%100%
NetflixFriends360%75%
Amazon PrimeDhamaal360%100%
Amazon PrimeHindi240%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!

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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)