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.
I am trying to calculate a % of the items that were completed within a given target date, this being the SLA.
The data has a number of columns, but the ones I need for my calculations are :
Item # | Completion date | Target Date | SLA |
1 | 15/08/2020 | 20/08/2020 | Met |
2 | 17/08/2020 | 15/08/2020 | Not Met |
3 | 20/08/2020 | 20/08/2020 | Met |
4 | 23/08/2020 | 20/08/2020 | Not Met |
5 | 01/09/2020 | 01/09/2020 | Met |
From the above sample, I want to get is the % of items that have met SLA = 60% (3 out of 5).
I am creating a new measure so that I can then display the result using a VISUAL (Card).
I have checked similar posts in the forum, but all of them are using SUM to first arrive at the total and then divide the result. This doesn't seem to work for me as I do not want the SUM of the column SLA, but a COUNT.
Solved! Go to Solution.
@Anonymous , Try a measure like
Divide(countx(Table, if([Completion date] <=[Target Date], [Item #], blank())),count(Table[Item #]))
Divide(countx(Table, if([SLA] = "Met", [Item #], blank())),count(Table[Item #]))
Where column
SLA= if( [Completion date] <=[Target Date], "Met", "Not Met")
Hi @Anonymous ,
Please see the GIF. There is two ways that can change it to percentage format.
@Anonymous , Try a measure like
Divide(countx(Table, if([Completion date] <=[Target Date], [Item #], blank())),count(Table[Item #]))
Divide(countx(Table, if([SLA] = "Met", [Item #], blank())),count(Table[Item #]))
Where column
SLA= if( [Completion date] <=[Target Date], "Met", "Not Met")
HI @amitchandak ,
Thanks for your solution, it is working and I am getting the correct results now. Earlier, my calculated column SLA in my original data table was referring to an incorrect column.
I have therefore modified my reply.
Can you please let me know how should I display it as a % rather than a number. This is how it currently appears when I use the data card visual
Hi,
This meaure will work
=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Completion date]<=Data[Target Date]))/COUNTROWS(Data)
Hope this helps.
Hi @Ashish_Mathur,
I tried the solution, however, I got 1 as a result.
This is what appears on the VISUAL:
Hi,
Share the link from where i can download your PBI file and clearly show the page/visual where you want the result as 60%.
Hi @Ashish_Mathur ,
Thank you for your response. 😊
I have got the correct results using the solution provided by Amit.
Rgds,
Neeradhi
Hi @Anonymous ,
You could set percentage format in Measure tools.
Click the measure > choose % > change decimal places
Hi @Anonymous ,
Please see the GIF. There is two ways that can change it to percentage format.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |