Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

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

View solution in original post

Hi @Anonymous ,

 

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
Super User

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

Anonymous
Not applicable

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
Super User

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/
Anonymous
Not applicable

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/
Anonymous
Not applicable

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

 

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.
Anonymous
Not applicable

Hi @v-xuding-msft , 

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

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

@v-xuding-msft , 

 

Great the second option worked 👍

 

 

Thanks a lot 😀

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.