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

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.

Reply
SAM_
New Member

Calculated Column to show Percentage based on condition

Dear All,

 

I  am using below table to get percantage of the sprint 

 

SnoIssuesStatusSprint
1768DoneSprint6
2888DoneSprint6
3666QA in ProgressSprint6
4999Deployed in ProdSprint6
5567DoneSprint5
6900DoneSprint5
7100In-ProgressSprint5


would like to have a one calucalted column for the below table that tells about the perceantge of number of issues done in the each sprint  such as below

if (Table[Sprint]="Sprint6") than (total issues where status is done &  where sprint=6 )/( total issues where sprint=6) else

if (Table[Sprint]="Sprint5") than (total issues where status is done &  where sprint=5 )/( total issues where sprint=5) else
if (Table[Sprint]="Sprint4") than (total issues where status is done &  where sprint=5 )/( total issues where sprint=4) else

 

Can any one please help me how can I make it.

 

Regards,
SAM_

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

These measures work

Total = COUNTROWS(Data)
Done = CALCULATE([Total],Data[Status]="Done")
Done (%) = DIVIDE([Done],[Total])

Hope this helps.

Ashish_Mathur_0-1712115675700.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Ritaf1983
Super User
Super User

Hi @SAM_ 
If I understood you correctly and the goal is to show the percentage of "Done" by sprint you can use the measures :
1.

Count_done = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Sprint]),'Table'[Status]="Done")
2.
Percent_of_done = if(max('Table'[Status])="Done", divide([Count_done],CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Sprint]))),"")
Result:
Ritaf1983_0-1712115727459.png

if it is something else plese

Pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

 

View solution in original post

2 REPLIES 2
Ritaf1983
Super User
Super User

Hi @SAM_ 
If I understood you correctly and the goal is to show the percentage of "Done" by sprint you can use the measures :
1.

Count_done = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Sprint]),'Table'[Status]="Done")
2.
Percent_of_done = if(max('Table'[Status])="Done", divide([Count_done],CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Sprint]))),"")
Result:
Ritaf1983_0-1712115727459.png

if it is something else plese

Pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

 

Ashish_Mathur
Super User
Super User

Hi,

These measures work

Total = COUNTROWS(Data)
Done = CALCULATE([Total],Data[Status]="Done")
Done (%) = DIVIDE([Done],[Total])

Hope this helps.

Ashish_Mathur_0-1712115675700.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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