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

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

Hi tborg,

 

Could you please share the original data please? A dummy one is enough. I think function Summarize could help.

 

Best Regards,

Dale

  • 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