Calculate number of clients above a certain value

[atrigodaroza]

Hello everyone,

I'm having trouble with this measure. I would like to calculate the number of clients with a reception rate above 75%. Here's a simplified version of my dataset :

Client	Month of package	reception Status
BFA	01/22	Received
COL	01/22	Not received
BFA	02/22	Received
COL	02/22	Received

So basically, I would like to show with a measure how many clients have a received more than 75% of all packages (one package a month). In this simple example, the measure would have "1" as a result, as only one client is above the 75% reception mark.

It would be even better if the result would be in % of all clients. So the final result would be 50%, as in 50% of clients have a received more than 75% of all their packages.

Thank you very much in advance for your help. I hope this is clear 🙂

Antonio Trigo da Roza

[FreemanZ · Accepted solution]

hi @atrigodaroza 

try to write a measure like this:

75PlusClientRate = 
VAR _table = 
ADDCOLUMNS(
    VALUES(TableName[Client]),
    "Rate",
    VAR _received =
    CALCULATE(
        COUNT(TableName[reception status]),
        TableName[reception status]="Received"
    )
    VAR _allstatus = 
    CALCULATE(
        COUNT(TableName[reception status])
    )    
    RETURN
    DIVIDE(_received, _allstatus)
)
VAR _ClientCount = COUNTROWS(VALUES(TableName[Client]))
VAR  _75PlusClientCount = 
COUNTROWS(FILTER(_table, [Rate]>=0.75))
RETURN 
DIVIDE(_75PlusClientCount, _ClientCount)

With your data sample, it worked like this:

[FreemanZ · Accepted solution]

hi @atrigodaroza 

try to write a measure like this:

75PlusClientRate = 
VAR _table = 
ADDCOLUMNS(
    VALUES(TableName[Client]),
    "Rate",
    VAR _received =
    CALCULATE(
        COUNT(TableName[reception status]),
        TableName[reception status]="Received"
    )
    VAR _allstatus = 
    CALCULATE(
        COUNT(TableName[reception status])
    )    
    RETURN
    DIVIDE(_received, _allstatus)
)
VAR _ClientCount = COUNTROWS(VALUES(TableName[Client]))
VAR  _75PlusClientCount = 
COUNTROWS(FILTER(_table, [Rate]>=0.75))
RETURN 
DIVIDE(_75PlusClientCount, _ClientCount)

With your data sample, it worked like this:

[atrigodaroza]

Wow! Thank you so much, this worked perfectly.

Only added a "+0" at the end so the result is never null.

Thanks again @FreemanZ !

Antonio

[FreemanZ · Accepted solution]

hi @atrigodaroza 

the intermediate count measure is like:

75PlusClientCount = 
VAR _table = 
ADDCOLUMNS(
    VALUES(TableName[Client]),
    "Rate",
    VAR _received =
    CALCULATE(
        COUNT(TableName[reception status]),
        TableName[reception status]="Received"
    )
    VAR _allstatus = 
    CALCULATE(
        COUNT(TableName[reception status])
    )    
    RETURN
    DIVIDE(_received, _allstatus)
)
VAR _ClientCount = COUNTROWS(VALUES(TableName[Client]))
VAR  _75PlusClientCount = 
COUNTROWS(FILTER(_table, [Rate]>=0.75))
RETURN 
_75PlusClientCount

[amitchandak]

@atrigodaroza , If you displaying at the client level

Divide( Countrows(filter(Table, Table[reception Status] = "Received")), Countrows(Table) )