Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
Solved

Calculated Column Subject to Context (Z-Score Calculation)

Hello

I've been trying to create a calculated column to get the Z-Score of a piece of data segmented by a variable.

I have managed to arrive at this formula (which I leave at the end), which gives me the Z-scores of the "Population" column based on the "Language spoken" column (there are a large number of rows that refer to this data depending on the region, country, etc.)

However, I can't get the column to be subject to context, as I apply other filters in the table (filter as a visual) and the calculations don't change. That is, I want to filter by another field, in this case, "Country", to see the Z-scores of each "Region" and that these Z are recalculated for the specific country (and not calculated on the total sample, which is what happens to me now).

z score =
were _variable = CALCULATE(SELECTEDVALUE(DB[Language spoken]))
were _Average = CALCULATE(AVERAGE(DB[Population]),FILTER(ALL(DB),DB[Language spoken]=_variable))
were _DesvT = CALCULATE(STDEV. S(DB[Population),FILTER(ALL(DB),DB[Language spoken] =_variable))
return
DIVIDE(DB[Population]-_Average, _DesvT)
Thanks a lot
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Syndicate_Admin 

     

    "ALL" does not apply the slicer, you can try changing it to "ALLSELECTED". "ALLSELECTED" will retain the slicer filter and remove other filters.

     

    Please try the following:

     

    Create a measure.

    Measure = 
var _Average = 
    CALCULATE(
        AVERAGE('Table'[Population]), 
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Language spoken] in {[Language spoken]}
        )
    )
var _DesvT = 
    CALCULATE(
        STDEV.S('Table'[Population]), 
        FILTER(
            ALLSELECTED('Table'), 
            'Table'[Language spoken] in {[Language spoken]}
        )
    )
RETURN DIVIDE(SELECTEDVALUE('Table'[Population]) - _Average, _DesvT)

     

    Here is the result.

     

     

    Regards,

    Nono Chen

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

     

     

3 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    Hello

    Here is an example of how the data would be distributed.

    RegionCountryLanguage spokenPopulation
    EuropaSpainSpanishxxxx
    EuropaSpainCatalanxxxx
    EuropaSpainBasquexxxx
    EuropaSpainCatalanxxxx
    AmericaArgentinaSpanishxxxx
    AmericaChileSpanishxxxx
    AmericaBrazilPortuguesexxxx
    AmericaDominican Republic Spanishxxxx
    AmericaDominican Republic Frenchxxxx
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Syndicate_Admin 

     

    "ALL" does not apply the slicer, you can try changing it to "ALLSELECTED". "ALLSELECTED" will retain the slicer filter and remove other filters.

     

    Please try the following:

     

    Create a measure.

    Measure = 
var _Average = 
    CALCULATE(
        AVERAGE('Table'[Population]), 
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Language spoken] in {[Language spoken]}
        )
    )
var _DesvT = 
    CALCULATE(
        STDEV.S('Table'[Population]), 
        FILTER(
            ALLSELECTED('Table'), 
            'Table'[Language spoken] in {[Language spoken]}
        )
    )
RETURN DIVIDE(SELECTEDVALUE('Table'[Population]) - _Average, _DesvT)

     

    Here is the result.

     

     

    Regards,

    Nono Chen

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