Reply
Frequent Visitor
Posts: 11
Registered: ‎08-14-2018
Accepted Solution

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): 

forum 3.png

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!


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.

 

Untitled.png

View solution in original post


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,

 

Thank you for your answer,

 

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

forum 5.png

 

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:

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

 

I hope it will help you resolving my problem.

 

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,

 

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

How_to_calculate_a_percentage_with_condition_and_group_by

 

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.

 

Untitled.png

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

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

@Ashish_Mathur,

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.

 

@v-jiascu-msft,

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