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 there
Probably an easy one for the seasoned PowerBi developer. I'm a newbie.
we looking to find percentage for column status where value is "OnTime". I already have a table for date dimension which we'll use to visualize data by month
Date | Status |
01-Mar | OnTime |
04-Mar | Late |
04-Apr | Undetermined |
07-Apr | Late |
05-Jun | OnTime |
08-Jul | OnTime |
01-Aug | Late |
03-Aug | Undetermined |
Solved! Go to Solution.
Hello,
You can use this DAX measure to achieve it:
%OnTimeStatus = DIVIDE(CALCULATE(COUNTA(StatusTable[Status]),FILTER(StatusTable,StatusTable[Status] = "OnTime")),CALCULATE(COUNTA(StatusTable[Status]),ALLEXCEPT(StatusTable,StatusTable[Status])))
Also ensure to click on the measure & change the format to percentage.
This will give you the percentage of On Time on the basis of total rows in your table.
I hope I interpreted your requirement correctly.
Please respond back if you are looking for something else
Sure you can. Both will work for you.
Infact COUNTROWS will give more efficient result for you.
Hello,
You can use this DAX measure to achieve it:
%OnTimeStatus = DIVIDE(CALCULATE(COUNTA(StatusTable[Status]),FILTER(StatusTable,StatusTable[Status] = "OnTime")),CALCULATE(COUNTA(StatusTable[Status]),ALLEXCEPT(StatusTable,StatusTable[Status])))
Also ensure to click on the measure & change the format to percentage.
This will give you the percentage of On Time on the basis of total rows in your table.
I hope I interpreted your requirement correctly.
Please respond back if you are looking for something else
Could i have used
%OnTimeStatus = DIVIDE(CALCULATE(COUNTROWS(StatusTable),StatusTable[Status] = "OnTime"),CALCULATE(COUNTROWS(StatusTable)))
THis seems to give me a different result which looks more accurate tho
Sure you can. Both will work for you.
Infact COUNTROWS will give more efficient result for you.
This has been super useful to a problem I am trying to tackle as well however I need to exclude the "blanks" in this column prior to the calculation. How would I edit the measure to achieve this?
You can use COUNTROWS DAX function with an expression. Something like below:
%OnTimeStatus = DIVIDE(CALCULATE(COUNTROWS(FILTER(StatusTable,StatusColumn <> null)),StatusTable[Status] = "OnTime"),CALCULATE(COUNTROWS(StatusTable)))
Used:
(COUNTROWS(FILTER(StatusTable,StatusColumn <> null))
Try:
Measure = DIVIDE(CALCULATE(COUNTROWS(FILTER('Psych data','Psych data'[Appointment required]<> BLANK())),'Psych data'[Appointment required]="Yes"),CALCULATE(COUNTROWS('Psych data')))
That's resulted in the same figure unfortunately.
Thankfully though I have managed to sort it; with some help from a work colleague. I really appreciate you taking the time though.
Thanx PC2790
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 |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |