cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
neeradhi
Helper II
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 #

Completion

date

Target DateSLA
115/08/202020/08/2020Met
217/08/202015/08/2020Not Met
320/08/202020/08/2020Met
423/08/202020/08/2020Not Met
501/09/202001/09/2020Met

 

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
amitchandak
Super User IV
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")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Hi @neeradhi ,

 

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

4.gif 

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.

View solution in original post

10 REPLIES 10
amitchandak
Super User IV
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")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

neeradhi_0-1599454484614.png

 

 

 

Ashish_Mathur
Super User III
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
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur

 

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

 

neeradhi_0-1599393142956.png

This is what appears on the VISUAL: 

 

neeradhi_1-1599393192023.png

 

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
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur , 

 

Thank you for your response. 😊

 

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

 

Rgds, 

Neeradhi

Hi @neeradhi ,

 

You could set percentage format in Measure tools.

 

Click the measure > choose % > change decimal places

 

v-xuding-msft_0-1599616826792.png

 

 

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.

Hi @v-xuding-msft , 

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

Hi @neeradhi ,

 

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

4.gif 

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.

View solution in original post

@v-xuding-msft , 

 

Great the second option worked 👍

 

 

Thanks a lot 😀

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.