Forum Discussion

LarryChen's avatar
LarryChen
New Member
2 years ago
Solved

Switch measure not working properly

I create a mresure – sum_amount = SUM(transactions_agg[amount])

And sum_amount will be grouped if I select Year or Month column or not.

Example:

userID year, month, sum_amount

1,2024,01,100000

1,2024,02,20000

1,2023,12,1000

And then I create a measure to define group of sum_amount.

amount_group =

switch(True,    

[sum_amount]<=50000, "<=50k",    

[sum_amount]>50000 && [sum_amount]<=100000, "50k - 100k",

[sum_amount]>100000 && [sum_amount]<=150000,"100k - 150k",

 ">150k")

When I put this measure into the table:

userID year, sum_amount

1,2024,100000, "50k - 100k"

1,2024,20000, "<=50000"

1,2023,1000, "<=50000"

The desired output should be :

userID year, sum_amount

1,2024,120000,"100k - 150k"

1,2023,1000, "<=50000"

How should I modify the measure?

Need Help

4 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    2 years ago

    Hi LarryChen  Please make sure in your table, you have placed User ID, Year, Sum_Measure not Amount Column, and amount_group measure. If you have placed amount column then make sure, summarization method is Sum. See the below image:

     

     

    If not solve please recreate the table again. You measure are correct.

    Here is my output:

     

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution!!

     

     

    Best Regards,
    Shahariar Hafiz

     

    • LarryChen's avatar
      LarryChen
      New Member
      2 years ago

      Hi, 

      I use Amount column 

      and the switch function  = 

      amount_group = switch(True,
          sum(transactions_agg[amount])<=50000, "<=50k",
          sum(transactions_agg[amount])>50000 && sum(transactions_agg[amount])<=100000, "50k - 100k",
          sum(transactions_agg[amount])>100000 && sum(transactions_agg[amount])<=150000,"100k - 150k",
          ">150k")
       
      But the function is not working properly 

       

       

      • shafiz_p's avatar
        shafiz_p
        Super User
        2 years ago

        Have you tried creating new table?  Your formula bypassing all the condition and evalutes only other condition.

        Try changing column with the measure, which sum of amount. Also, try this amount_group measure :

        amount_group =
        VAR Total = SUM('Table'[ Amount])

        VAR Result =
        switch(
            TRUE(),    
            Total <=50000, "<=50k",    
            Total > 50000 && Total <=100000, "50k - 100k",
            Total >100000 && Total <=150000,"100k - 150k",
            ">150k"
        )

         RETURN
         Result
         
        It is perfectly working for dummy data you have provided.

        OR, Try creating a calculated column, using the given formula :

        Amount_Group =
        VAR YearTotal =
            CALCULATE(
                SUM('Table'[ sum_amount]),
                ALLEXCEPT('Table', 'Table'[ year])
            )
         
        VAR Result =
        SWITCH        (
            TRUE(),
            YearTotal <= 50000, "<=50k",
            YearTotal > 50000 && YearTotal <= 100000, "50k - 100k",
            YearTotal > 100000 && YearTotal <= 150000, "100k - 150k",
            ">150k"
        )

        RETURN
        Result
         
        The above formula, will label sum of amount based on year. For all the same year value will have a same label. For example, all rows with year 2024, will have same label, because we are summing up year wise. 

        Note : This will label same for all different user ID. Either remove user ID from table, if you want only year wise. But if you want to segmentize user ID, and Year, then add user ID column inside ALLEXCEPT() Column.

        Hope this helps!!