Forum Discussion
Helper IAccumulating within categories
Hi tborg,
You also can add it in the Query Editor like this. Please note the tips in the snapshot.
Best Regards,
Dale
Dale, you rock! I never would have guessed about the single quote!
Thanks a million!
Tom
Helper ICorrection:
I was just reviewing the sample data, and realized I did not explain correctly what I weant to do.
In this data, there are 17 advisors. Four have <25 plans. Six are in the 25-49 category, 5 in the 50-74 category, 2 in the 125-149 category, and 1 in the 175-199 category. It looks like this:
<25 4
25+ 6
50+ 5
75+ 0
100+ 0
125+ 1
150+ 0
175+ 1
200+ 0
What I want to show is how many are in a category AND ALL CATEGORIES ABOVE THAT:
<25 4
25+ 13 (the sum of 6+5+1+1)
50+ 7 (the sum of 5+1+1)
75+ 2 (the sum of 1+1)
100+ 2
125+ 2
150+ 1
175+ 1
200+ 0
Sorry for the confusion!
Thanks.
Microsoft EmployeeHi tborg,
You can try it out in this file.
1. Add a conditional column in the Query Editor.
2. Then create a measure like this.
Accumulating =
IF (
MIN ( 'Table1'[Index] ) = 1,
COUNT ( Table1[Grand Total] ),
CALCULATE (
COUNT ( 'Table1'[Grand Total] ),
FILTER ( ALL ( 'Table1' ), 'Table1'[Index] >= MIN ( 'Table1'[Index] ) )
)
)
Best Regards,
Dale
- tborg
Thanks! This might work, but I'm having a problem with your "Category" column. My equivalent column was a custom column created in the Desktop, so it is not available when I create the conditional column. So I first created a new conditional column to create the Cagtegory column, but it never registers anything above "25+".
How did you creaate your "Category" column?
(I could not view your attached file, as it can't get through our firewall.)
Thx!
- v-jiascu-msft
Hi tborg,
You also can add it in the Query Editor like this. Please note the tips in the snapshot.
Best Regards,
Dale
- tborg
Dale, you rock! I never would have guessed about the single quote!
Thanks a million!
Tom