Forum Discussion

shermayne123's avatar
shermayne123
Helper I
3 years ago
Solved

Filter Matrix for 1 column

Hi,

 

I've created a Matrix visualization for 3 survey questions with percentages for Agree/Strongly Agree and Others responses. I added Question to the Rows, Reclassified Responses to the Columns and selected the Values to show percent of row total(see below screenshot). I want to show Agree/Strongly Agree percentages only. But when I filtered for this in the Filter section by checking only Agree/Strongly Agree in the Reclassified Responses, the percent became 100% which was not what I wanted. I'm expecting them to remain at the same percentage as when I have both types of responses(Agree/Strongly Agree and Others) i.e. 64.71% and 90%. Is there a way to have these percentages displayed without the Others column? 

 

Before filter is applied:

 

After filter is applied:

 

 

 

 

 

Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    Hi shermayne123 ,

     

    This is my test table:

     

    Please try following measure to create a new column:

    Percentage = 
var _count =  CALCULATE(COUNT('Table'[Reclassified Responses]),FILTER('Table','Table'[Question]=EARLIER('Table'[Question]) && 'Table'[Reclassified Responses] = EARLIER('Table'[Reclassified Responses])))
var _allcount = COUNTX(FILTER('Table','Table'[Question] = EARLIER('Table'[Question])),'Table'[Reclassified Responses])
return
DIVIDE(_count,_allcount)

     

    Create a matrix visual:

    After filtering:

     

    Please refer the attached pbix file.

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    shermayne123 , use all in the denominator measure

    example

    divide([m1], calculate([m2], all(Table[Question])) )

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • shermayne123's avatar
      shermayne123
      Helper I
      3 years ago

      Hi amitchandak,

       

      Don't quite know how to adjust the vizualization using your line of code. Can you kindly enlighten? 

      Below are the screenshots of my data table :

       

      And the sample output desired i.e. the matrix visualization with the desired percentages:

      Hope that clarifies. Please let me know if otherwise, thank you!

       
       

       

       

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    Hi shermayne123 ,

     

    This is my test table:

     

    Please try following measure to create a new column:

    Percentage = 
var _count =  CALCULATE(COUNT('Table'[Reclassified Responses]),FILTER('Table','Table'[Question]=EARLIER('Table'[Question]) && 'Table'[Reclassified Responses] = EARLIER('Table'[Reclassified Responses])))
var _allcount = COUNTX(FILTER('Table','Table'[Question] = EARLIER('Table'[Question])),'Table'[Reclassified Responses])
return
DIVIDE(_count,_allcount)

     

    Create a matrix visual:

    After filtering:

     

    Please refer the attached pbix file.

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.