Trying to change a Calculated Column category to a Measure

[Anonymous]

Apologies in advance if this question is too simple, however I'm struggling to find an answer to this myself as a power bi newbie. I am creating many calculated columns and I am struggling to convert them into measures.

For example, in a sales table, I want to add a sales category:

Sales Category Calculated Column = SWITCH(True, Sales[Total] <= 100, "Low Value", Sales[Total] <= 1000, "Mid Value", Sales[Total] > 1000, "High Value")

This will essentially add on a calculated column, giving each sale a low/med/high category. I can then generate charts, using the count of key as the value, and the sales category as the legend, and show splits of low, med and high sales. I find this easy and intuitive - but its obviously adding a lot of data and columns to the model.

How would I do the equivalent of something like this using Measures?

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your description, you need to add aggregate functions like sum, max, and min to convert column to measure. I did the test reference as follows:

M_Result =
VAR a =
    CALCULATE ( SUM ( 'Table'[Total] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) )
RETURN
    IF (
        a <= 100,
        "Low Value",
        IF ( a > 100 && a <= 1000, "Mid Value", "High Value" )
    )

M_1 =
VAR a =
    MAX ( 'Table'[Total] )
RETURN
    IF (
        a <= 100,
        "Low Value",
        IF ( a > 100 && a <= 1000, "Mid Value", "High Value" )
    )

For more details, you can read related blog:

Calculated Columns and Measures in DAX - SQLBI

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous The equivalent measure would be:

Sales Category Calculated Column = 
  SWITCH(TRUE(), 
    SUM(Sales[Total]) <= 100, "Low Value", 
    SUM(Sales[Total]) <= 1000, "Mid Value", 
    SUM(Sales[Total]) > 1000, "High Value"
  )

Column references in measures must have an aggregation like MAX, SUM, etc. Now, measures have restrictions on their use in axis, legends, etc. You may find the Disconnected Table Trick handy in those instances as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563