Forum Discussion

setis's avatar
setis
Post Partisan
7 years ago
Solved

Measure vs. calculated column

Hi All,    Despite making an effort in trying to understand when to use a calculated column and when a measure, I findt it difficult sometimes.    I've made a calculated column to segment amounts...
  • AlB's avatar
    7 years ago

    Hi setis

     

    Determining segments like this is usually more useful with a calculated column as you have it but you can certainly do it with a measure as well. Set Financial[Amount Paid] in the rows of a matrix visual the measure to be placed in values of the visual would be as below. Note that all we've done is substitute SELECTEDVALUE(Financial[Amount Paid]) for Financial[Amount Paid]).  In a measure there is no row context and therefore the reference to the naked column Financial[Amount Paid] makes no sense. You need to make use of the filter context in the current row of the visual to get the desired value through SELECTEDVALUE( ) 

     

    GroupValues2_Measure = 
    SWITCH (
        TRUE ();
        SELECTEDVALUE(Financial[Amount Paid]) >0
        && SELECTEDVALUE(Financial[Amount Paid]) <=5000;"a.0-5.000";
            SELECTEDVALUE(Financial[Amount Paid]) >5000
        && SELECTEDVALUE(Financial[Amount Paid]) <=10000;"b.5.000-10.000";
            SELECTEDVALUE(Financial[Amount Paid]) >10000
        && SELECTEDVALUE(Financial[Amount Paid]) <=30000;"c.10.000-30.000";
            SELECTEDVALUE(Financial[Amount Paid]) >30000
        && SELECTEDVALUE(Financial[Amount Paid]) <=50000;"d.30.000-50.000";       
        SELECTEDVALUE(Financial[Amount Paid]) >50000
        && SELECTEDVALUE(Financial[Amount Paid]) <=100000;"e.50.000-100.000";
        SELECTEDVALUE(Financial[Amount Paid]) <=0;"f.Adjustments";
        SELECTEDVALUE(Financial[Amount Paid]) >100000;"f.>100.000"
    )

    You can also make use of variables to make the code a bit more readable and maintainable, and execute SELECTEDVALUE only once:

     

    GroupValues2_Measure_V2 =
    VAR _CurrentRowAmount =
        SELECTEDVALUE ( Financial[Amount Paid] )
    RETURN
        SWITCH (
            TRUE ();
            _CurrentRowAmount > 0
                && _CurrentRowAmount <= 5000; "a.0-5.000";
            _CurrentRowAmount > 5000
                && _CurrentRowAmount <= 10000; "b.5.000-10.000";
            _CurrentRowAmount > 10000
                && _CurrentRowAmount <= 30000; "c.10.000-30.000";
            _CurrentRowAmount > 30000
                && _CurrentRowAmount <= 50000; "d.30.000-50.000";
            _CurrentRowAmount > 50000
                && _CurrentRowAmount <= 100000; "e.50.000-100.000";
            _CurrentRowAmount <= 0; "f.Adjustments";
            _CurrentRowAmount > 100000; "f.>100.000"
        )