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