Percentage of values that are above a certain value grouped

[Anonymous]

Good afternoon, 

I am trying to make a report for counting the chronic absence for participants in our program. They are considered chronic if they are absent 10% or more of the time. I currently have the report able to show average attendance rate per site and overall for the organization. I am trying to now show how many are chronic out of total participants in a way where it is [chronically absent]/[total participants] for the period displayed as a percentage. So 687/1438 would display 47%.

I am using a direct query model. I have a column that tells me if the participant is considered present for that particular day and another column that tells me if the participant is absent for that particular day. 

How would I create a field on the report that would report the [total chronic absent]/[total participant]?

Sample data looke like this:

ID     CountAsAbsent   CountAsPresent   AttendanceDate

001   0                          1                          20200101

002   0                          1                          20200101

003   1                          0                          20200101

001   1                          0                          20200102

002   0                          1                          20200102

003   1                          0                          20200102

001   0                          1                          20200103

002   0                          1                          20200103

003   1                          0                          20200103

From the above data I would like the result to be 66% or 67% rounding doesn't really matter much.

[vivran22 · Accepted solution]

Hello @Anonymous 

You may try this:

% Critical =
VAR Table1 =
    SUMMARIZE (
        dtTable,
        dtTable[ID ],
        "TotalPresent", SUM ( dtTable[   CountAsPresent] ),
        "TotalAbsent", SUM ( dtTable[   CountAsAbsent] )
    )
VAR Countpc =
    SUMX (
        Table1,
        IF ( DIVIDE ( [TotalAbsent], [TotalPresent] + [TotalAbsent] ) > .10, 1 )
    )
VAR CriticalRatio =
    DIVIDE ( Countpc, DISTINCTCOUNT ( dtTable[ID ] ) )
RETURN
    CriticalRatio

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[Anonymous · Accepted solution]

Thanks @vivran22,your solution is exactly what I'm looking for in theory. However, when I applied this to my dataset, I'll get a number that exceeds 11K. After looking at this equation for more than a day, IT MUST work in theory, however, I'm not getting what I would expect.

Maybe if I explain my data a little more someone could tell me why they're doing what they're doing.

The data has several years of data in it (20110801 - present), in which we report on certain months or other date ranges. For example, right now I'm trying to report on 20191101 - 20191130. I expect the result to be very close to 47% (0.47). However, I am currently getting 11,397.14 of the formula.

This particular report has a slicer for the date range in it, which from my understanding would filter the range of data that would use this formula. I would also expect the formula to return a number > 1 since it is a fraction. What could be going on here?

Thanks for all your help!

[vivran22 · Accepted solution]

Hello @Anonymous 

You may try this:

% Critical =
VAR Table1 =
    SUMMARIZE (
        dtTable,
        dtTable[ID ],
        "TotalPresent", SUM ( dtTable[   CountAsPresent] ),
        "TotalAbsent", SUM ( dtTable[   CountAsAbsent] )
    )
VAR Countpc =
    SUMX (
        Table1,
        IF ( DIVIDE ( [TotalAbsent], [TotalPresent] + [TotalAbsent] ) > .10, 1 )
    )
VAR CriticalRatio =
    DIVIDE ( Countpc, DISTINCTCOUNT ( dtTable[ID ] ) )
RETURN
    CriticalRatio

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[Anonymous · Accepted solution]

Thanks @vivran22,your solution is exactly what I'm looking for in theory. However, when I applied this to my dataset, I'll get a number that exceeds 11K. After looking at this equation for more than a day, IT MUST work in theory, however, I'm not getting what I would expect.

Maybe if I explain my data a little more someone could tell me why they're doing what they're doing.

The data has several years of data in it (20110801 - present), in which we report on certain months or other date ranges. For example, right now I'm trying to report on 20191101 - 20191130. I expect the result to be very close to 47% (0.47). However, I am currently getting 11,397.14 of the formula.

This particular report has a slicer for the date range in it, which from my understanding would filter the range of data that would use this formula. I would also expect the formula to return a number > 1 since it is a fraction. What could be going on here?

Thanks for all your help!

[Anonymous · Accepted solution]

Alright, after much digging I found that calculated tables and columns do not respond to slicers. Summarize creates a calculated table as a result, so the slicer doesn't actually take effect into it. I found the solution here.