Rank colum

[H1r0ka]

There are follwoing three tables. Customer table and Sales table are joined with cusomter ID and customer table has 'Sales Total' column.

I'd like to add a rank colum to Cusomter table

I've tried to use '= witch ( TRUE(),[Sales Total]=0,"Minor",[Sales Total]>100,"General",[Sales Total >500],"Big" )   

However all rank colum result shows "Minor".  How to add a rank colum to Customer table?

Customer table,

Customer Nane	Customer ID
A	001
B	002
C	003

Sales table,

Date	Sales	Customer ID
20-Aug	100	003
21-Aug	200	002
22-Aug	50	003
23-Aug	500	002
24-Aug	300	001

Rank table

Rank	Type
0>	Minor
100>	General
500>	Big

[v-piga-msft · Accepted solution]

Hi @H1r0ka,

You could create a calculated column with the formula below.

Column =
VAR sales_total =
    CALCULATE ( SUM ( Sales[Sales] ) )
RETURN
    IF (
        AND ( sales_total >= 0, sales_total < 100 ),
        "Minor",
        IF (
            AND ( sales_total > 100, sales_total < 500 ),
            "General",
            IF ( sales_total > 500, "Big" )
        )
    )

Then you could get the output like this.

Best Regards,

Cherry

[v-piga-msft · Accepted solution]

Hi @H1r0ka,

You could create a calculated column with the formula below.

Column =
VAR sales_total =
    CALCULATE ( SUM ( Sales[Sales] ) )
RETURN
    IF (
        AND ( sales_total >= 0, sales_total < 100 ),
        "Minor",
        IF (
            AND ( sales_total > 100, sales_total < 500 ),
            "General",
            IF ( sales_total > 500, "Big" )
        )
    )

Then you could get the output like this.

Best Regards,

Cherry

[H1r0ka · Accepted solution]

Hi Cherry,

Thank you very much for your solution.

すごいね！

Thank you again,

H1r0ka