cancel
Showing results for
Did you mean:
Highlighted
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 Date Time 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/2016 14-30 days 7/8/2016 14-30 days 7/8/2016 14-30 days 7/4/2016 14-30 days
1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

Re: Percent based on qtr instead of grand total

Do you want to do the calculation like below?

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.
2 REPLIES 2
Microsoft

Re: Percent based on qtr instead of grand total

Do you want to do the calculation like below?

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

Announcements

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors