cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leroy773 Helper I
Helper I

Percent based on qtr instead of grand total

All,

 

I am attempting to calculate the percent of 3 different groups in a single qtr for the current ytd.  Unfortunately, the calculation only does grand total.  Is there a way to calculate based on qtr.  Sample of table below

 

Closed DateTime To Close
4/26/2016>30 days
4/19/2016>30 days
6/6/2016<14 days
1/25/2016<14 days
2/23/2016<14 days
1/26/2016<14 days
6/27/201614-30 days
7/8/201614-30 days
7/8/201614-30 days
7/4/201614-30 days
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Percent based on qtr instead of grand total

Hi @leroy773

 

Do you want to do the calculation like below?

2.PNG

 

If that is the case, you need to add a calculated column named "Qtr":

Qtr = 'Percent'[Closed Date].[Quarter]

 

Create three measures to get the percentage value:

 

CountRowsOfQrt = CALCULATE(COUNTROWS('Percent'),ALLEXCEPT('Percent','Percent'[Qtr]))
CountRowsOfQrtandTime = CALCULATE(COUNTROWS('Percent'),ALLEXCEPT('Percent','Percent'[Qtr],'Percent'[Time To Close]))

Percentage = [CountRowsOfQrtandTime]/[CountRowsOfQrt]

 

If I have something misunderstood, please describe more clear about how to calculate the percentage and you can port some screenshots of your expect result.

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
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

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Percent based on qtr instead of grand total

Hi @leroy773

 

Do you want to do the calculation like below?

2.PNG

 

If that is the case, you need to add a calculated column named "Qtr":

Qtr = 'Percent'[Closed Date].[Quarter]

 

Create three measures to get the percentage value:

 

CountRowsOfQrt = CALCULATE(COUNTROWS('Percent'),ALLEXCEPT('Percent','Percent'[Qtr]))
CountRowsOfQrtandTime = CALCULATE(COUNTROWS('Percent'),ALLEXCEPT('Percent','Percent'[Qtr],'Percent'[Time To Close]))

Percentage = [CountRowsOfQrtandTime]/[CountRowsOfQrt]

 

If I have something misunderstood, please describe more clear about how to calculate the percentage and you can port some screenshots of your expect result.

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
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

leroy773 Helper I
Helper I

Re: Percent based on qtr instead of grand total

Thanks this helped, but found a limitation if I use a slicer.  When i filter the report the slicer calculates percentage incorrectly since the slicer impacts the total count.  Is there a way around this?

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

Top Solution Authors
Top Kudoed Authors