Calculated Column Subject to Context (Z-Score Calculation)

[Syndicate_Admin]

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 · Accepted solution]

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.

[Syndicate_Admin]

Hello

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

Region	Country	Language spoken	Population
Europa	Spain	Spanish	xxxx
Europa	Spain	Catalan	xxxx
Europa	Spain	Basque	xxxx
Europa	Spain	Catalan	xxxx
America	Argentina	Spanish	xxxx
America	Chile	Spanish	xxxx
America	Brazil	Portuguese	xxxx
America	Dominican Republic	Spanish	xxxx
America	Dominican Republic	French	xxxx

[Ritaf1983]

Hi @Syndicate_Admin 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523