The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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 |
Solved! Go to Solution.
Hi @leroy773
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
Hi @leroy773
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
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?
User | Count |
---|---|
160 | |
111 | |
96 | |
86 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |