How to calculate a percentage with condition and group by?

[Anonymous]

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!

[Anonymous]

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

[Anonymous]

Hi @Anonymous,

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

[Anonymous]

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

[Anonymous]

Hi @Anonymous,

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

[Anonymous]

Hi @Anonymous,

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?

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

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

Hope this helps.

[Anonymous]

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

[Ashish_Mathur]

You are welcome.

[Anonymous]

This is the correct formula I found since I wanted to divide by the number of buildings and not by the number of batchs:

COUNTROWS(FILTER(VALUES(Operation[OPERATION]); [Nb réservations]<=3))/COUNTROWS(VALUES(Operation[OPERATION]))*100

[v-jiascu-msft]

Hi @Anonymous,

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

[Stachu]

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