Changing Sales Ranks Depending on Slicer

[StatsChick]

Hi, 

  Please do train me if I'm not asking with the right syntax.

I have a table:

Customer ID  Sales  Category

1                     $1000    Foo

1                          $40    Bar

2                          $50    Foo

5                         $100  Foo

5                         $10    Bar

I'd like to put on a ranking list, like this:

If SUM([sales]) >= 100,"Fabulous","Ordinary"

But I need the ranks to change when I use a slicer outside of the table to select parts of the table. So if I select "Foo", I'd get:

CustomerID  Category   Rank          Total Sales

1                   Foo            Fabulous    $1000

2                    Foo            Ordinary     $50

5                   Foo           Oridinary      $100

I've tried creating a table but the values remain fixed. I've tried lots of SELECT variants, but don't know how to compate a value against a visualized table. 

Any help?

--StatsChick

[v-henryk-mstf]

Hi @StatsChick ,

According to the information you currently provide, there may be a problem with the creation of your Rank measure. I did the following test, which can be used as a reference. The created Rank is based on the clientID classification to obtain sum_sales for judgment. Get a summary table, and then filter according to category slicer, everything shows normal.

Total sales = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[client]))

Rank = IF([Total sales]>=100,"Fabulous","Ordinary")

If the problem is still not resolved, please provide detailed error information and right demand. Let me know immediately, looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StatsChick]

Thank you for taking a look, Henry. However, I have mixed categories for each client. So client 1 has $40 if you choose the Bar category, and would then be marked Ordinary; but has $100 in the Foo category, and would be marked Fabulous if I am choosing either all categories or just Foo.

I need the sum to re-sum according to my several slicers, and then I need a dynamic ranking variable.

Does that make sense?

[aj1973]

Hi @StatsChick 

Check this out.

https://drive.google.com/file/d/1iA1wfFcIgYD9S3CBy1T3LlPuVVvTTBIk/view?usp=sharing

[StatsChick]

Allow me to be more thorough in my question. I have this data:

client     Sales    Category

1            $50       Foo

1            $100     Bar

2            $1000   Foo

3             $10      Bar

5             $50      Foo

5              $100   Bar

What I need is a summary table, which is easy to achieve, including the computed if/then (over $100) statement to discern fabulousness:

clientID          Sales      Rank    

1                   $150        Fabulous

2                   $1000      Fabulous

3                   $10         Orindary

5                   $150       Fabulous

However, if I choose "Foo" in the Category slicer, as Amine nicely put together in a sample for me, I need the Rank to recompute on the fly, like this example where I chose "Bar".

clientID       Sales      Rank

1                 $100      Fabulous 

2                  $0         Ordinary

3                  $10       Ordinary

5                  $100     Fabulous

Does this make sense?

I've tried CALCULATEDTABLE but it doesn't change values when I use slicers. I have about 10 slicers on this data. 

I've tried the SELECTED statements to no avail. 

What do you all think? I don't care if the answer is a table or pivot that recomputes with the slicer selections or a visualization, as long as I can show that last summary table example. 

[StatsChick]

Hi, Amine,

  Thanks for working up a sample for me.  But I have many slicers and I need the summary to sum up by ID when I show all categories. so, when no categories are chosen on the slicer, for instance, ID's 1 and 5 should have a summed sales total.