- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-17-2018 07:09 AM

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.

Thank you in advance for your help!

Solved! Go to Solution.

Accepted Solutions

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-26-2018 08:12 PM

Hi,

This measure works

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

Hope this helps.

All Replies

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-17-2018 08:02 AM

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

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-17-2018 08:03 AM - edited 08-17-2018 08:04 AM

@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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-17-2018 08:26 AM

Hi @mnayar,

Thank you for your answer,

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-17-2018 08:34 AM

@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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-20-2018 12:56 AM

Hi @mnayar,

Here is the link of my powerbi file:

https://drive.google.com/file/d/1oemfJJP-AY-Hj8nNT-peUNPL5jnEu4SP/view?usp=sharing

I hope it will help you resolving my problem.

Thank you

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-22-2018 07:32 AM

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?

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-26-2018 07:23 PM

Hi @Op37_alice,

Try this formula, please.

Poucentage opération inf 3 resa New = VAR numop = DISTINCTCOUNT ( Operation[OPERATION] ) VAR numopinf = SUMX ( ADDCOLUMNS ( 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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-26-2018 08:12 PM

Hi,

This measure works

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

Hope this helps.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-27-2018 12:25 AM

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...