Complex DAX Queries to Calculate Z-Scores with Filterable Results

[Anonymous]

Hi all,

I have a calculated table which consists of individuals and indicators associated with them. The indicators are represented by yes/no questions which the individuals either fit or don't, hence they either score a 1 or 0 to designate if they fit this indicator or not. The table is in long format to allow the user to filter which indicator(s) they want to examine. The format of it is as follows, however there are considerably more rows and characteristics: 

ID	Indicator	Count (yes or no)
1	AAA	1
2	AAA	1
3	AAA	1
4	AAA	1
5	AAA	1
6	AAA	0
7	AAA	0
8	AAA	0
9	AAA	0
10	AAA	0
1	BBB	1
2	BBB	1
3	BBB	1
4	BBB	0
5	BBB	0
6	BBB	1
7	BBB	1
8	BBB	0
9	BBB	0
10	BBB	0

The ID column within this table has a relationship to a master lookup table which contains personal information about the person, i.e. age band, location, etc. This allows the user to filter based on these on the page. The intention of this is to allow the user to ultimately see the number of filtered individuals (by age, etc) in different locations who hit the indicators, i.e. how many people aged 25-29 in location Z-ZZ have indicator AAA. The nature of some of these indicator is sensitive and as a result I would like to calculate a z-score for the different locations on the fly using a measure. The difficulty is that I want the user to still be able to filter the individuals present in the data by age, location, indicator, and have a z-score calculated on the fly for the resulting column.

Locations within the dashboard start at a low level and can be aggregated up to a higher level, I want this still to be possible for my z-score measure. For example, I may have the following aggregated table on my page:

Lower Level Location	Indicator	Summed Count
Z-ZZ	AAA	100
Z-YY	AAA	150
Y-ZZ	AAA	200
Y-YY	AAA	100
X-ZZ	AAA	300
X-YY	AAA	50
Z-ZZ	BBB	150
Z-YY	BBB	200
Y-ZZ	BBB	50
Y-YY	BBB	100
X-ZZ	BBB	200
X-YY	BBB	100

A user may then filter this table to show only individuals aged 25-29 and the values in [Summed Count] will change. I want the Z-Score to reflect these changes but am having trouble with calculating the averages and standard deviations across the different indicators properly. This is how I want the Z-Score to be calculated, I have added the Mean and St.Dev columns in just to represent the calculation steps I want but these will likely end up being stored in a VAR within a single Z-Score measure.

Lower Level Location	Indicator	Summed Count	Mean	Sd.Dev	Z-Score
Z-ZZ	AAA	100	150	81.6	-0.61
Z-YY	AAA	150	150	81.6	0
Y-ZZ	AAA	200	150	81.6	0.61
Y-YY	AAA	100	150	81.6	-0.61
X-ZZ	AAA	300	150	81.6	1.84
X-YY	AAA	50	150	81.6	-1.23
Z-ZZ	BBB	150	133.3	55.2	0.30
Z-YY	BBB	200	133.3	55.2	1.21
Y-ZZ	BBB	50	133.3	55.2	-1.51
Y-YY	BBB	100	133.3	55.2	-0.60
X-ZZ	BBB	200	133.3	55.2	1.21
X-YY	BBB	100	133.3	55.2	-0.60

As shown I want the Z-Scores to be calculated across locations for different indicators, hence the mean and Sd.Dev are calculated from all the values in the [Summed Count] columns for each indicator respectively.

The main difficulty, and the reason that I cannot use a calculated table for this, is that I still want users to be able to filter a table like the above by different characteristics of individuals, with new Z-Scores being calculated each time. I also want the Z-Score measure to be able to be used when looking at higher level locations like so: 

Higher Level Location	Indicator	Summed Count	Mean	Sd.Dev	Z-Score
Z	AAA	250	300	40.8	-1.23
Y	AAA	300	300	40.8	0
X	AAA	350	300	40.8	1.23
Z	BBB	350	266.6	85.0	0.98
Y	BBB	150	266.6	85.0	-1.37
X	BBB	300	266.6	85.0	0.39

Again, a table like the above needs to be able to be filtered by things like age band and therefore needs to originally come from an aggregated form of the first table shown. The final result will likely be that the individual can select one or many filters and the Z-Scores will be summed for a specific location to find areas which consist of out of the ordinary numbers of individuals hitting a certain indicator, i.e Location Z would score -0.25, Y would score -1.37 and X would score 1.62. But that should be a fairly simple case of just summing the values created by [Z-Score]. 

I have been working at this for days and have got close but just can't quite figure the problem out. I know that the answer likely lies in a somewhat complex DAX measure consisting of SUMX(), AVERAGEX(), STDEV.PX(), CALCULATE() and FILTER() but I can't figure out the right way to do it. 

I would really appreciate any help I can get with this. Let me know if any further info is needed.

Thanks in advance.

EDIT

As requested I have attached a sample powerbi document. I have tried to lay out the data in a very similar way to my actual data, with relationships laid out in the same way. The table indicators has calculated columns which rely on conditions from other data tables, just like in my actual document. The layout of relationships and filters is very similar to that of my actual data.

EXAMPLE DATA 

[DataInsights]

@Anonymous,

Try this measure. The pattern can be adapted for Lower Level Location, standard deviation, etc.

Mean Upper Level Location = 
VAR vBaseTable =
    CALCULATETABLE (
        SUMMARIZE (
            'Indicators Long',
            'Locations Lookup'[Upper Level Location],
            'Indicators Long'[Indicator]
        ),
        ALLEXCEPT ( 'Indicators Long', 'Indicators Long'[Indicator] )
    )
VAR vCalcTable =
    ADDCOLUMNS ( vBaseTable, "@SummedCount", [Summed Count] )
VAR vResult =
    AVERAGEX ( vCalcTable, [@SummedCount] )
RETURN
    vResult

-----

[Anonymous]

Hi DataInsights,

thanks for your reply, I assume that it's not possible to write a piece of DAX which doesn't need to be changed for the lower level locations? I.e. A single measure which works in both tables without having to be changed for each?

Also, the calculated mean (in this example) keeps the same result even when you filter on different upper level locations within the dashboard. I need it to dynamically change so that it calculates the mean if only (for example) 2 upper level locations are selected. 

Thanks.

[PaulOlding · Accepted solution]

You can use ISINSCOPE to see if you should be summarizing by lower or upper level.

ALLSELECTED instead of ALLEXCEPT makes the measure responsive to upper level slicer

Mean = 
VAR vResult =
IF(
    ISINSCOPE('Locations Lookup'[Lower Level Location]),
    AVERAGEX(
        CALCULATETABLE (
            SUMMARIZE (
                'Indicators Long',
                'Locations Lookup'[Lower Level Location],
                'Indicators Long'[Indicator]
            ),
            ALLSELECTED('Locations Lookup'[Lower Level Location])
        ), 
        [Summed Count]
    ),
    AVERAGEX(
        CALCULATETABLE (
            SUMMARIZE (
                'Indicators Long',
                'Locations Lookup'[Upper Level Location],
                'Indicators Long'[Indicator]
            ),
            ALLSELECTED('Locations Lookup'[Upper Level Location])
        ), 
        [Summed Count]
    )
)
RETURN
    vResult

[Anonymous]

That's fantastic thank you, that has worked perfectly. 

My final question relating to this, how do I now create a measure which sums the Z-Scores for a single location if I have two or more indicators selected on the page. As in:

In the right hand table above, I would like the Z-Scores to be as follows:

Lower Level Location	Summed Count	Z-Score
X-YY	0	-13.4
X-ZZ	67	1.19
Y-YY	54	-1.94
Y-ZZ	57	-1.14
Z-YY	72	1.02
Z-ZZ	67	0.87

These numbers are derived from summing the values for each location and will need to be dynamic depending on which indicators are selected on the page. I would ideally like the behaviour to be consistent for a table containing upper level locations as well.

Is this possible?

Thanks

[Anonymous]

Hi all,

I figured this out myself, so for those revisiting the thread, the final Z-Score code I put together was the following: 

Z-Score = 

VAR _mean =
IF(
    ISINSCOPE('Locations Lookup'[Lower Level Location]),
    AVERAGEX(
        CALCULATETABLE (
            SUMMARIZE (
                'Indicators Long',
                'Locations Lookup'[Lower Level Location],
                'Indicators Long'[Indicator]
            ),
            ALLSELECTED('Locations Lookup'[Lower Level Location])
        ), 
        [Summed Count]
    ),
    AVERAGEX(
        CALCULATETABLE (
            SUMMARIZE (
                'Indicators Long',
                'Locations Lookup'[Upper Level Location],
                'Indicators Long'[Indicator]
            ),
            ALLSELECTED('Locations Lookup'[Upper Level Location])
        ), 
        [Summed Count]
    )
)

VAR _stdev =
IF(
    ISINSCOPE('Locations Lookup'[Lower Level Location]),
    STDEVX.P(
        CALCULATETABLE (
            SUMMARIZE (
                'Indicators Long',
                'Locations Lookup'[Lower Level Location],
                'Indicators Long'[Indicator]
            ),
            ALLSELECTED('Locations Lookup'[Lower Level Location])
        ), 
        [Summed Count]
    ),
    STDEVX.P(
        CALCULATETABLE (
            SUMMARIZE (
                'Indicators Long',
                'Locations Lookup'[Upper Level Location],
                'Indicators Long'[Indicator]
            ),
            ALLSELECTED('Locations Lookup'[Upper Level Location])
        ), 
        [Summed Count]
    )
)
VAR _zscore =
DIVIDE ( [Summed Count] - _mean , _stdev )

RETURN
_zscore

I was then able to sum the results of these in the way I described above using the following code:

Summed Z-Scores = 
SUMX ( VALUES ('Indicators Long'[Indicator] ) , [Z-Score] )

Thanks all for your help with this issue, I've learnt so much!