Forum Discussion

siddgupta's avatar
siddgupta
New Member
8 years ago

Calculating proportion based on selection

Hi,

 

I am trying to add a column to a matrix visual that looks to caputure the number of times someone is reported as "top 2 box" or "don't know" and then divide that by the total number of people that fit under that criteria based on fitlers like "audience" ,"geo"

, "Company", "Wave"  as opposed to the total row count 

 

top 2 box is classified by a new column that has the following logic: Metric Value = IF(OR(' Merge'[Score] = "7",  Merge'[Score] = "6"), "Top 2 Box", IF(' Merge'[Score] = "99", "Don't Know"))

 

Here's the information for the matrix:

Rows: attributes

Columns: Metric Value (coming from the column mentioned above)

Values:  count of metric values

 

 

So just to clarify, in the above matrix add an additional column that takes that 124 value and divides by the total sample for that based on the filters (not the total column, or the full sample size of respondents)

 

 

 

Amazon Merge Table columns

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Try this measure

     

    =COUNTROWS(Data)/CALCULATE(COUNTROWS(Data),ALLEXCEPT(Data,Data[Metric Value]))

    • siddgupta's avatar
      siddgupta
      New Member
      8 years ago

      Hi Ashish,

       

       

      That Ddd not give me the anticipated result. Below I have included a screenshot of the full dashboard with filters - hiding the company filter. The current numbers in the matrix are the values based on the audience filtered to 'elite' and the Geo filtered to 'US'. As previously mentioned, I created a criteria for Don't Know & Top 2 Box. For example, in this scenario there should be 250 people that statisfy that audience, company, geo, and wave filter. I need to find the percentages of these out of that 250 that fit the criteria. The value for top 2 box in the 5th row of my matrix should be 145/250 = 58%. I hope that makes more sense?

       

      Rows: Attribute

      Column: Metric Value

      Values: Count of Metric Valus

       

      Dashboard with Filters on the Left and up top and values displayed by Metric Value