Show Percentage of Matrix Rows above X

[dinoscool3]

I have data with registrations for the past two years, each registration is attached to a group and I've created a matrix with how many registrations each group has.

I want to see how many Groups have above 50 regsitrations. I've tried doing a mutlitude of functions but they all either find a blank, 1, or count every organization regardless of whether they are over 50.

One solution I tried was adding a measure column and used IF(count('Schools over 5 registrations'[email])>50,1,0) to see if I could get a work around. This correctly shows 1 for all groups over 50, but the "total" listed at the bottom is 1, and I can't seem to get SUM or SUMX to work with the measure.

I'm sure there's a very simple solution that I will facepalm myself for not thinking on, but could anyone assist me here?

Thank you!

[v-eqin-msft · Accepted solution]

Hi @dinoscool3 ,

According to my understanding , you want to show how many organizations have more than 50 regsitrations,right?

You could try this:

CountFlag = IF( CALCULATE(COUNT('Table'[Email]),ALLEXCEPT('Table','Table'[Organization ]))>5,1,0)

CountAbove50 = IF(ISINSCOPE('Table'[Organization ]),[CountFlag], CALCULATE(DISTINCTCOUNT('Table'[Organization ]),FILTER('Table',[CountFlag]=1)))

The final output is shown below:

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

[v-eqin-msft · Accepted solution]

Hi @dinoscool3 ,

According to my understanding , you want to show how many organizations have more than 50 regsitrations,right?

You could try this:

CountFlag = IF( CALCULATE(COUNT('Table'[Email]),ALLEXCEPT('Table','Table'[Organization ]))>5,1,0)

CountAbove50 = IF(ISINSCOPE('Table'[Organization ]),[CountFlag], CALCULATE(DISTINCTCOUNT('Table'[Organization ]),FILTER('Table',[CountFlag]=1)))

The final output is shown below:

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

[dinoscool3]

Sorry for the late reply. This worked, almost. I assumed that the relative date filter I had in effect for the page would also work with the CountFlag function you listed, but it seems it didn't. I've tried adding in a dates in period function using my date table:

Schools over 50 = IF(CALCULATE(CALCULATE(COUNT('Schools over 5 registrations'[email]),ALLEXCEPT('Schools over 5 registrations','Schools over 5 registrations'[fmwmgkyrny groups.organization_name]))>50,DATESINPERIOD('Date',TODAY(),-90,DAY)),1,0)

but I get the following error: "DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument."

Is there any easier way to keep the date filter in tact for the Count Flag?

[Ashish_Mathur]

Hi,

Share some simple data, describe the question and show the expected result.

[dinoscool3]

I'm having trouble posting so here's a screenshot of my reply.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[dkaushik]

Hi @dinoscool3 ,

You can create a calculated column and assign 1 or 0 based on your criteria. Then create a measure to COUNT instead of SUM.

Hope this helps.

Thanks,

Dheeraj

[dinoscool3]

Hi! I've tried this too, but  I run into the same problem. I've already caluclated the 1 or 0 on my matrix, but it won't let me count a column made from a measure.