Forum Discussion
Accumulating within categories
Hello all!
I have a visualization that looks like this:
This is set up to count the number of advisors that meet the criteria in column 1 for number of plans sold (this is just a custom column that indicates the advisors meeting the critera for that category). Right now it is categorizing them as 25-49, 50-74, 75-99, etc. What I want is for the 2nd column to count ALL those with 25+ plans, then ALL those with 50+ plans, then ALL those with 75+ plans, etc.
So column 2 should look like this:
| 329 |
| 629 |
| 295 |
| 123 |
| 52 |
| 27 |
| 12 |
| 8 |
| 4 |
Here are some of my measures/columns:
Column:
Number of Plans =
IF(TotalPlans[Plans Grand Total] < 25, "< 25",
IF(TotalPlans[Plans Grand Total] > 24 && TotalPlans[Plans Grand Total] < 50, "25 +",
IF(TotalPlans[Plans Grand Total] > 49 && TotalPlans[Plans Grand Total] < 75, "50 +",
IF(TotalPlans[Plans Grand Total] > 74 && TotalPlans[Plans Grand Total] < 100, "75 +",
IF(TotalPlans[Plans Grand Total] > 99 && TotalPlans[Plans Grand Total] < 125, "100 +",
IF(TotalPlans[Plans Grand Total] > 124 && TotalPlans[Plans Grand Total] < 150, "125 +",
IF(TotalPlans[Plans Grand Total] > 149 && TotalPlans[Plans Grand Total] < 175, "150 +",
IF(TotalPlans[Plans Grand Total] > 174 && TotalPlans[Plans Grand Total] < 200, "175 +",
IF(TotalPlans[Plans Grand Total] > 199, "200 +")))))))))
Measures:
Number of Advisors = COUNTROWS ( TotalPlans )
Ave. Earnings = DIVIDE( SUM ( TotalEarnings[Earnings] ) , ( COUNTROWS( TotalEarnings ) ) ,0)
"Earnings" is just the data in the original source.
So I think I need a different calculation for "Number of Advisors" but don't know how to do it to get what I want. Any help is appreciated!
Thanks!
Tom
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
7 Replies
Hi tborg,
Could you please share the original data please? A dummy one is enough. I think function Summarize could help.
Best Regards,
Dale
Here is a sample of the data. The first column identifies the advisor, the 2nd column is the count of plans, where I have identified the groupings, "<25", "25+", "50+", etc. So in this sample, the count in category "25+" should be 827, in "50+" should be 605, in "125+" should be 315, and in "175+" should be 184.
I hope this is enough info.
Advisor ID Grand Total Category
14110 184 175+ 15474 29 25+ 14994 5 <25 03793 5 <25 01473 49 25+ 14930 56 50+ 15595 26 25+ 15194 16 <25 13189 58 50+ 01759 50 50+ 14267 48 25+ 03188 28 25+ 13846 66 50+ 07588 60 50+ 10220 42 25+ 13463 131 125+ 15169 1 <25 Thanks!
Correction:
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.