Forum Discussion

vvibhakar's avatar
vvibhakar
Frequent Visitor
8 years ago
Solved

Data Categorisation in a new column

Hello Everyone,   I want to have dynamic column which can group my client based on the sales. For example in below Table 1, I have my Client sales Value by month. I can easily represent this as wel...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    vvibhakar You don't need 3 tables. All you need are 2 calculations.

     

    Sales Per Client = 

    CALCULATE(SUM(Table1[Sales]), ALLEXCEPT(Table1,Table1[Client]))

     

    Sales Bucket Per Client = SWITCH(TRUE(),

                                             Sales Per Client >= 10000 && Sales Per Client <25000,"10-25K",

                                             Sales Per Client >= 25001 && Sales Per Client <40000,"26-40K",

                                             Sales Per Client >= 40001 && Sales Per Client <60000,"41-60K",

                                             "60+")

     

  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi vvibhakar,

     

    You could creata a measure like below.

     

    group =
VAR a =
    SUM ( 'Table'[Sales] )
RETURN
    IF (
        ISBLANK ( a ),
        BLANK (),
        IF (
            AND ( a > 10000, a < 25000 ),
            "10K-25K",
            IF (
                AND ( a > 25000, a < 40000 ),
                "25K-40K",
                IF (
                    AND ( a > 40000, a < 60000 ),
                    "40K-60K",
                    IF (
                        AND ( a > 60000, a < 75000 ),
                        "60K-75K",
                        IF ( AND ( a > 75000, a < 100000 ), "75K-100k", "Above100K" )
                    )
                )
            )
        )
    )

    Here is the output.

     

     

    Hope this can help you!

     

    Best Regards,

    Cherry

Anonymous's avatar
Anonymous
Not applicable
8 years ago

vvibhakar You don't need 3 tables. All you need are 2 calculations.

 

Sales Per Client = 

CALCULATE(SUM(Table1[Sales]), ALLEXCEPT(Table1,Table1[Client]))

 

Sales Bucket Per Client = SWITCH(TRUE(),

                                         Sales Per Client >= 10000 && Sales Per Client <25000,"10-25K",

                                         Sales Per Client >= 25001 && Sales Per Client <40000,"26-40K",

                                         Sales Per Client >= 40001 && Sales Per Client <60000,"41-60K",

                                         "60+")

 

  • vvibhakar's avatar
    vvibhakar
    Frequent Visitor
    8 years ago

    Anonymous
    This works great. But I still require a line over these buckets where i need my count of unique Clients in each bucket. The Unique count of either Clients or the Sales bucket per client displays wrong values.

    Can you also please tell me which way can i have that.

    Thanks
    Vivek

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      vvibhakar what is the formula you are using for the unique count of clients per bucket?

       

      it should be something like

       

      CALCULATE(DISTINCTCOUNT(Table1[Client]), ALLEXCEPT(Table1, Table1[Sales Bucket Per Client])

      • vvibhakar's avatar
        vvibhakar
        Frequent Visitor
        8 years ago

        I am not using any formula. I create a bar and line chart with the bucket as shared axis, sales value as bar/columns & then put client in line value and select Distinct count.
        You formula gives me correct values, but it replicates on each single line if I use it for a column. If I use it as a measure, it adds up due to transactions count as well and does not show exact client counts per sales bucket in a visual.

         

        Thanks

        Vivek