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...
Need Help
shafiz_p's avatar
shafiz_p
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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!!