## Desktop

Frequent Visitor
Posts: 11
Registered: ‎08-14-2018

# How to calculate a percentage with condition and group by?

Hi all,

I have a table containing different buildings, each building can have different batchs which can be booked or not.

I am trying to obtain the percentage of buildings where less than 3 batchs have been booked.

For example here I have, among 4 buildings, only one building which has more than 3 batchs booked (4):

In this case I expect the percentage to be 75%.

I don't manage to find the appropriate formula to calculate the percentage, I don't really know in DAX how to group by buildings with the condition.

Accepted Solutions
Super User
Posts: 3,177
Registered: ‎01-14-2017

## Re: How to calculate a percentage with condition and group by?

Hi,

This measure works

=COUNTROWS(FILTER(VALUES(Operation[OPERATION]),[Nb réservations]=0))/COUNTAX(Lots,[Nb réservations])

Hope this helps.

All Replies
New Contributor
Posts: 541
Registered: ‎06-23-2016

## Re: How to calculate a percentage with condition and group by?

you can use COUNTROWS & SUMMARIZE, can you past the sample of your data?

Established Member
Posts: 222
Registered: ‎07-13-2018

## Re: How to calculate a percentage with condition and group by?

[ Edited ]

@Op37_alice I will try to breakdown the solution into steps so you can understand better

1) Calculate number of batches per building

#ofBatchPerBuilding = CALCULATE(DISTINCTCOUNT([Batch#]), ALLEXCEPT(TableName, TableName[Code/Building Name]))

2) Find number of Buildings over all

# of Buildings = CALCULATE(DISTINCTCOUNT(TableName[Code/Building Name]), ALL(TableName))

3) Find number of Buildings with batch more than 3

# of Buildings more batch = CALCULATE(DISTINCTCOUNT(TableName[Code/Building Name]), ALL(TableName), FILTER(TableName, TableName[#ofBatchPerBuilding]>3))

4) Calculate %

= # of Buildings more batch/ # of Buildings

You could combine 2,3, & 4 step like this

% =

VAR  # of Buildings = CALCULATE(DISTINCTCOUNT(TableName[Code/Building Name]), ALL(TableName))

VAR  # of Buildings more batch = CALCULATE(DISTINCTCOUNT(TableName[Code/Building Name]), ALL(TableName), FILTER(TableName, TableName[#ofBatchPerBuilding]>3))

RETURN  # of Buildings more batch/ # of Buildings

If you need details of a function just google implementation for that in power bi. it is important to understand usage of allexcept and all in power bi. it is used quite a bit.

Frequent Visitor
Posts: 11
Registered: ‎08-14-2018

## Re: How to calculate a percentage with condition and group by?

Hi @mnayar,

I followed your solution but I don't have the expected result, I obtained 16.67% instead of 75%:

Here are my formula to calculate the number of batches per building:

Nb opérations = CALCULATE(DISTINCTCOUNT(Lots[Nombre_réservations]); ALLEXCEPT(Lots; Lots[OPERATION]))

and the formula to obtain the percentage:

Poucentage opération inf 3 resa =
VAR numop = CALCULATE(DISTINCTCOUNT(Lots[OPERATION]);ALL(Lots))
VAR numopinf = CALCULATE(DISTINCTCOUNT(Lots[OPERATION]);ALL(Lots);FILTER(Lots; Lots[Nb opérations]<3))
RETURN numopinf/numop*100

Established Member
Posts: 222
Registered: ‎07-13-2018

## Re: How to calculate a percentage with condition and group by?

@Op37_alice Can you post your power bi file so I can see what has been done. You can upload the file to google drive or dropbox and share the link and I can have you a solution right away. if you cant share data plz create a powerbi file with dummy data

Highlighted
Frequent Visitor
Posts: 11
Registered: ‎08-14-2018

## Re: How to calculate a percentage with condition and group by?

Hi @mnayar,

Here is the link of my powerbi file:

Thank you

Frequent Visitor
Posts: 11
Registered: ‎08-14-2018

## Re: How to calculate a percentage with condition and group by?

Hi @mnayar,

The problem probably comes from slicers I have in my report.

I tried to replace ALL by ALLSELECT to take into account the differents filters but it is not working, any idea?

Community Support Team
Posts: 6,249
Registered: ‎05-02-2017

## Re: How to calculate a percentage with condition and group by?

Hi @Op37_alice,

```Poucentage opération inf 3 resa New =
VAR numop =
DISTINCTCOUNT ( Operation[OPERATION] )
VAR numopinf =
SUMX (
SUMMARIZE ( Operation, Operation[OPERATION] ),
"NR", IF (
ISBLANK ( CALCULATE ( SUM ( [Nombre_réservations] ) ) ),
0,
CALCULATE ( SUM ( [Nombre_réservations] ) )
)
),
IF ( [NR] <= 3, 1, 0 )
)
RETURN
numopinf / numop
* 100
```

Best Regards,

Dale

Super User
Posts: 3,177
Registered: ‎01-14-2017

## Re: How to calculate a percentage with condition and group by?

Hi,

This measure works

=COUNTROWS(FILTER(VALUES(Operation[OPERATION]),[Nb réservations]=0))/COUNTAX(Lots,[Nb réservations])

Hope this helps.

Frequent Visitor
Posts: 11
Registered: ‎08-14-2018

## Re: How to calculate a percentage with condition and group by?

Thank you for your help, I changed your formula to Nb réservations <= 3 so that it's more suitable and it's working.

I will try to understand all functions you used.

I tried to use your formula but one of the last part is not working : [NR] is not recognized as a column in the IF statement...