Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hi,
This measure works
=COUNTROWS(FILTER(VALUES(Operation[OPERATION]),[Nb réservations]=0))/COUNTAX(Lots,[Nb réservations])
Hope this helps.
@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.
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 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
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
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?
Hi,
This measure works
=COUNTROWS(FILTER(VALUES(Operation[OPERATION]),[Nb réservations]=0))/COUNTAX(Lots,[Nb réservations])
Hope this helps.
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...
You are welcome.
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
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
you can use COUNTROWS & SUMMARIZE, can you past the sample of your data?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |