Forum Discussion

tborg's avatar
tborg
Icon for Helper I rankHelper 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
Icon for Microsoft Employee rankMicrosoft 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
Icon for Helper I rankHelper 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
    Icon for Microsoft Employee rankMicrosoft 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

    • tborg's avatar
      tborg
      Icon for Helper I rankHelper I
      8 years ago

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

       

      Thanks a million!

      Tom