Matrix: add frequency and hide when frequency <7

[Anonymous]

I made a matrix to show the mean score per item (EA-PI column) for each of the genders (male, female, other).

 I am now looking for a way to show the frequency of the genders as well. So for example to make the header look like this: Male (n=15). I tried adding a tooltip but then the frequency only shows up if you hover over the scores, while I want to see it when I hover over the header, and I'd rather not have a tooltip on the mean scores.

I would also like to hide columns if their frequency is below 7 participants. For all my other visuals I used the following line of code:

IS Valid Group = IF(CALCULATE(DISTINCTCOUNT(OrigineleData[Respondent]) > 7; ALLSELECTED(OrigineleData)) = TRUE(); 1; 2)

and then used a filter on the visuals so they only show data when IS Valid Group = 1. However, this does not seem to work on the matrix. 

Thank you for your help!

[v-lid-msft]

Hi @Anonymous ,

When use Allselected function, all the cells will return same result, Could you please try the following measure? 

IS Valid Group =
IF ( DISTINCTCOUNT ( OrigineleData[Respondent] ) > 7; 1; 2 )

If it doesn't meet your requirement, Could you please provide a mockup sample based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to OneDrive For Business and share the link here.

Please don't contain any Confidential Information or Real data in your reply.

Best regards,

[Anonymous]

Thank you for your reply @v-lid-msft, unfortunately your formula doesn't seem to work either. I uploaded my report on one drive for business here.

All data in this report are fake. The matrix I was referring to is on page "Details gender".

[v-lid-msft]

Hi @Anonymous ,

Based on my research with sample model you have shared, we find the each row (items column) has the same Unique Response ID from 0 to 186, so the distinct count for each cells in matrix are large than 7, we think it is the reason why measure does not work.

For example, when items = "Persoonlijk Welzijn" and S3 = 1, the will be 28 distinct value in the “Respondant" column of origin table, does the 28 means the desire frequency?

Best regards,

[Anonymous]

@v-lid-msft thank you for your reply!

28 is indeed the frequency I was talking about. But how can I hide or empty the cell "Persoonlijk welzijn x s3=1" if the frequency would be below 7? 

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to create a measure as value field of matrix to meet your requirement:

Measure = If([IsValidGroup] = 1, Average('Table'[Value]), BLANK())

(s3=7 was hidden when frequence reduce)

Best regards,

[Anonymous]

Sorry for my late reply.

Your solution seems to work when you do it, the images you posted are indeed what I'm trying to achieve. I edited this reply because at first I got error messages using your formula. But I got that to work now.

Only problem is that the entire matrix goes blank now. And I can't make it appear again using this measure, so something is not right. Why does it work when you do it? Can you send me the file you did this in?

Best regards,

Jade

[v-lid-msft]

Hi @Anonymous ,

Have you tried to change the [IS Valid Group] measure to following one we shared before?

IS Valid Group = 
IF ( DISTINCTCOUNT ( OrigineleData[Respondent] ) > 7,1, 2 )

Please also change the background color of measure, because replace the value, condition format will reset and I remember the text color of your matrix is white.

Best regards,

[Anonymous]

It's working! It was the white background indeed... I didn't realize that the conditional formatting would disappear. 

I didn't even have to change the measure.

Thank you so much! 🙂