Categorising and labeling based on whats above and bellow values

[Anonymous]

Hello

I need so help

I am trying to give certain supplyers a label based on sale value. 

In this example i whant the once with value bellow 10 to have C, above 10 to have B and above 20 to have A like shown in the second example. i need this to show in a matrix nad it is based on hundres of thousands of rows so i think i needs to be made in a measure using sum?

[Anonymous]

Hi @Anonymous,

You can use belwo formula(calculate column) to get the result category:

Category =
IF ( [Amount] < 10, "C", IF ( [Amount] < 20, "B", "A" ) )

If you only want to use measure, you can try to use below measure version.

Category(Measure) =
VAR current_Amount =
    SELECTEDVALUE ( Test2[Amount] )
RETURN
    IF ( current_Amount < 10, "C", IF ( current_Amount < 20, "B", "A" ) )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous

Thank you for youre anwere

Is see now that i dide not post a valid example. I dide forget to show that the supplyers do show up multiple times, and i need the total sum of the supplyer to be the value to work with.

Is it possible to make the measure somthing along the lines of:

Category(Measure) =
VAR current_Amount =
SELECTEDVALUE ( test2[amount] )
RETURN
IF(SUM(test2[amount] < 1000000; "C";  IF(SUM(test2[amount] < 2000000; "B"; "A" ))))

and take into account only the last 12 months?

[Anonymous · Accepted solution]

Hi @Anonymous,

You can use below measure to get the summary category:

Category(Measure) =
VAR current_supplier =
    LASTNONBLANK ( 'Table'[Supplier], [Supplier] )
VAR last_date =
    LASTDATE ( ALLSELECTED('Table'[Date] ))
VAR total_amount =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Supplier] = current_supplier
                && [Date]
                    >= DATE ( YEAR ( last_date ), MONTH ( last_date ) - 12, DAY ( last_date ) )
        ),
        [Amount]
    )
RETURN
    IF ( total_amount < 1000000, "C", IF ( total_amount < 2000000, "B", "A" ) )

How to use: Create table visual with 'supplier', 'amount' and above measure, switch 'amount' column's summary mode to 'sum'.

Regards,

Xiaoxin Sheng