Forum Discussion

tborg's avatar
tborg
Helper I
8 years ago
Solved

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 custo...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi tborg,

     

    You also can add it in the Query Editor like this. Please note the tips in the snapshot.

    Accumulating_within_categories3

     

    Best Regards,

    Dale

  • tborg's avatar
    tborg
    8 years ago

    Dale, you rock!  I never would have guessed about the single quote!

     

    Thanks a million!

    Tom

tborg's avatar
tborg
Helper I
8 years ago

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

14110184175+
154742925+
149945<25
037935<25
014734925+
149305650+
155952625+
1519416<25
131895850+
017595050+
142674825+
031882825+
138466650+
075886050+
102204225+
13463131125+
151691<25

 

Thanks!

tborg's avatar
tborg
Helper I
8 years ago

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.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi tborg,

     

    You can try it out in this file.

    1. Add a conditional column in the Query Editor.

    Accumulating_within_categories

    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] ) )
    )
)

    Accumulating_within_categories22

     

    Best Regards,

    Dale

    • tborg's avatar
      tborg
      Helper I
      8 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi tborg,

         

        You also can add it in the Query Editor like this. Please note the tips in the snapshot.

        Accumulating_within_categories3

         

        Best Regards,

        Dale