How to check category or rate sale data by Sum value?

[PowerBi888]

Dear all,

                 My issued is I would like to give Customer rate by sale value

Example data

Customer     ,   Customer-Type    ,   SaleValue , Month

A                  ,   Small                    ,   1000         , January

A                  ,   Small                    ,   2000         , February

B                  ,   Big                        ,   2500        , January

B                  ,   Big                        ,   3500        , February

                  I would likre return column like this :-

Customer   ,   Customer-Type    ,   SaleValue , RateValue

A                ,   Small                    ,   3000        , Good

B                ,   Big                       ,   4500        , Very Good

From 2 group condition :-

1.Customer-Type ="Small" -: if SaleValue < 1000 = "Low   , if SaleValue >=1000 and <2000 then "Average" , if SaleValue>=2000 then "Good"

2.Customer-Type ="Big" :- if SaleValue < 1500 = "Low   , if SaleValue >=1500 and <2500 then "Average" , if SaleValue>=2500 and  SaleValue<3500 then "Good" , if SaleValue>=3500 then"Very Good"

                   I can not find any solution , please give me any idea, thank you in advance.:-)

[TheoC · Accepted solution]

Hi @PowerBi888 

You can use SWITCH TRUE to achieve this:

Measure = 
VAR _1 = SUM ( Table[SaleValue] )
VAR _Small =
SWITCH ( 
     TRUE () ,
        _1 < 1000 , "Low" ,
        _1 < 2000 , "Average" ,
        _1 < >2000, "Good" )
VAR _Big =
SWITCH ( 
     TRUE () ,
        _1 < 1500 , "Low" ,
        _1 < 2500 , "Average" ,
        _1 < 3500, "Good"
        _1 < 3500, "Very Good")
RETURN 
IF ( Table[Customer-Type] = "Small" , _Small, _Big )

I haven't tested syntax but hopefully it works fine.

Theo 🙂

[PowerBi888]

Hi TheoC,

            Now I got it and solved above. I have one question if I would like to create Slicer for select value--> "Low" , "Average" How can I use Slicer for measure?

[TheoC]

Hi @PowerBi888 

Okay, create a calculated column that applies the same logic as the measure I created earlier using the below:

New Calculated Column = 
VAR _1 = CALCULATE ( SUM ( Table[SaleValue] ) , ALLEXCEPT ( 'Table' , Table[Customer] ) )
VAR _Small =
SWITCH ( 
     TRUE () ,
        _1 < 1000 , "Low" ,
        _1 < 2000 , "Average" ,
        _1 < >2000, "Good" )
VAR _Big =
SWITCH ( 
     TRUE () ,
        _1 < 1500 , "Low" ,
        _1 < 2500 , "Average" ,
        _1 < 3500, "Good"
        _1 < 3500, "Very Good")
RETURN 
IF ( Table[Customer-Type] = "Small" , _Small, _Big )

 Where the above works as a column, then all you do is drag "New Calculated Column" as the slicer.

Hope this helps 🙂

Theo

[TheoC]

@PowerBi888 off the top of my head, you'll need to put together a new table with the values in a column (ie low, avg, good, v good). I'll reply this morning when in front of computer. I just want to test to make sure.

[PowerBi888]

Hi TheoC,

             I try with new formula with column calculate value ,but it's not work. Error show look like "A circular dependency was detected in : New Column.

No idea to solved this issued.

[TheoC · Accepted solution]

@PowerBi888 a new column won't work. However you should be able to create a new table (even if you use Enter Data to achieve this). then, you can create relationship between the new table column and the Calculated Column.  Then just drag new column (from new table) as slicer.

Hope this helps.

[PowerBi888]

Hi TheoC,

            I try create new table with new Column rate, but I can not create relation new column with existing measure. How can I do?

Thank a lot .

[PowerBi888]

Hi TheoC,

                Now I got solution filter--> 

Power BI - Filter by a Measure in a Slicer

Power BI Filter by a Measure in a Slicer from youtube (BI Elite)

Thank you & hope help to others.

[TheoC · Accepted solution]

Hi @PowerBi888 

You can use SWITCH TRUE to achieve this:

Measure = 
VAR _1 = SUM ( Table[SaleValue] )
VAR _Small =
SWITCH ( 
     TRUE () ,
        _1 < 1000 , "Low" ,
        _1 < 2000 , "Average" ,
        _1 < >2000, "Good" )
VAR _Big =
SWITCH ( 
     TRUE () ,
        _1 < 1500 , "Low" ,
        _1 < 2500 , "Average" ,
        _1 < 3500, "Good"
        _1 < 3500, "Very Good")
RETURN 
IF ( Table[Customer-Type] = "Small" , _Small, _Big )

I haven't tested syntax but hopefully it works fine.

Theo 🙂