topic Re: Calculate number of clients above a certain value in DAX Commands and Tips

Calculate number of clients above a certain value

Anonymous — Mon, 23 Jan 2023 11:57:51 GMT

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

Re: Calculate number of clients above a certain value

amitchandak — Mon, 23 Jan 2023 12:09:16 GMT

Anonymous , If you displaying at the client level

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

Re: Calculate number of clients above a certain value

FreemanZ — Mon, 23 Jan 2023 12:20:13 GMT

hi Anonymous

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:

Re: Calculate number of clients above a certain value

FreemanZ — Mon, 23 Jan 2023 12:22:13 GMT

hi Anonymous

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

Re: Calculate number of clients above a certain value

Anonymous — Mon, 23 Jan 2023 14:29:42 GMT

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