Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |