Forum Discussion

dinoscool3's avatar
dinoscool3
Icon for Helper II rankHelper II
5 years ago
Solved

Show Percentage of Matrix Rows above X

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    dinoscool3
    Icon for Helper II rankHelper II
    5 years ago

    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?