cancel
Showing results for
Did you mean:
Helper II

## calculate a % of the total items

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

2 ACCEPTED SOLUTIONS
Super User IV

@neeradhi , 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")

Proud to be a Super User!

Community Support

Please see the GIF. There is two ways that can change it to percentage format.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 REPLIES 10
Super User IV

@neeradhi , 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")

Proud to be a Super User!

Helper II

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

Super User III

Hi,

This meaure will work

=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Completion date]<=Data[Target Date]))/COUNTROWS(Data)

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

I tried the solution, however, I got 1 as a result.

This is what appears on the VISUAL:

Super User III

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Thank you for your response. 😊

I have got the correct results using the solution provided by Amit.

Rgds,

Community Support

You could set percentage format in Measure tools.

Click the measure > choose % > change decimal places

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

I do not get this option, when I click on my measure

Community Support

Please see the GIF. There is two ways that can change it to percentage format.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

Great the second option worked 👍

Thanks a lot 😀

Announcements