DAX average score

[AnthNC]

Hi everyone,

Can you help me out with a measure please 

1/ Context

I'm doing a report on shop evaluations by mystery shoppers.

3 shops throughout 3 countries have been evaluated :

- Shops 1, 2 and 3 in Country 1

- Shops 4, 5 and 6 in Country 2

- Shops 7, 8 and 9 in Country 3

Each evaluated item is divided into subitems which can be marked 0,1 or 2. Each subitem's mark has a weighting :

Here's what the scores look like in a matrix table :

I've used this measure :

Score =

VAR SumMark = SUM(Marks[weighted mark])

VAR SumMaxMark = SUM(Marks[max weighted mark])

RETURN

DIVIDE(SumMark, SumMaxMark)

2/ Problem

Country 1 has the same call center for shops 1, 2 and 3.

Therefore my client would like to show the average score of the 3 shops for the item "Phone skills" while still showing each subitem specific score (not averaged).

So instead of having this :

I would like to have this :

The score of 67% represents Country 1's score :

What DAX fx can I use to achieve that ? Can someone help me out with this measure please ?

(i can't upload the pbx file)

Thank you

[Anonymous · Accepted solution]

Hi @AnthNC ,

Thanks for the reply from @belvoir99 , please allow me to provide another insight: 

You can change the expression for ScoreBelvoir99 to

ScoreBelvoir99 =
VAR SumMark = SUM(Marks[weighted mark])
VAR SumMaxMark = SUM(Marks[max weighted mark])
VAR SumMarkAllShops =
CALCULATE(
SUM(Marks[weighted mark]),
REMOVEFILTERS(Marks[shop])
)
VAR SumMaxMarkAllShops =
CALCULATE(
SUM(Marks[max weighted mark]),
REMOVEFILTERS(Marks[shop])
)
RETURN
IF(ISINSCOPE(Marks[subitem]),
DIVIDE(
SumMark.
SumMaxMark
), IF
IF(ISINSCOPE(Marks[evaluated item]) && SELECTEDVALUE(Marks[country])="Country 1" && SELECTEDVALUE(Marks[evaluated item])="Phone skills",
DIVIDE(
SumMarkAllShops,
SumMaxMarkAllShops
), DIVIDE(
DIVIDE(
SumMark, SumMaxMarkAllShops ), DIVIDE(
SumMaxMark
)
)
)

You can also change Score3 to

Score3 =
VAR SumMark = SUM(Marks[weighted mark])
VAR SumMaxMark = SUM(Marks[max weighted mark])
VAR SumMarkAllShops =
CALCULATE(
SUM(Marks[weighted mark]),
REMOVEFILTERS(Marks[shop])
)
VAR SumMaxMarkAllShops =
CALCULATE(
SUM(Marks[max weighted mark]),
REMOVEFILTERS(Marks[shop]))
VAR IsScopeSpecific = SELECTEDVALUE(Marks[country])="Country 1" && SELECTEDVALUE(Marks[evaluated item])="Phone skills" && NOT ISINSCOPE(Marks [subitem])
RETURN
IF(IsScopeSpecific.
DIVIDE(SumMarkAllShops, SumMaxMarkAllShops, SumMarkAllShops)
SumMaxMarkAllShops
),
DIVIDE(
SumMark, SumMaxMarkAllShops ), DIVIDE(
SumMaxMark
)
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[belvoir99 · Accepted solution]

Thanks for the PBIX file.

You are correct re ISINSCOPE. If you look at my code carefully you will see that there is a bracket after the column name e.g.

IF( ISINSCOPE(Marks[evaluated item]),

ISINSCOPE takes a column name as an argument i.e. Marks[evaluated item] and then returns a True or False value therefore your code returns an data type error as it is comparing a True/False value with a string value 'Country 1':

VAR IsScopeSpecific = ISINSCOPE(Marks[country])= "Country 1"

It looks like, in Score3, we have the correct calculation at the 'Phone skills' level but, at the 'evaluated item' level we want to revert back to the standard average.
I've tweaked my DAX measure a little bit - it's probably similar to or identical to Clara Gong's @Anonymous calculation - and now returns the correct calculation:

ScoreBelvoir99 = 
VAR SumMark = SUM(Marks[weighted mark])
VAR SumMaxMark = SUM(Marks[max weighted mark])
VAR SumMarkAllShops = 
	CALCULATE(
		SUM(Marks[weighted mark]),
		REMOVEFILTERS(Marks[shop])
	)
VAR SumMaxMarkAllShops = 
	CALCULATE(
		SUM(Marks[max weighted mark]),
		REMOVEFILTERS(Marks[shop])
	)
VAR StandardAverage =
 	DIVIDE(
		SumMark,
		SumMaxMark
	)
VAR AllShopsAverage =
    DIVIDE(
        SumMarkAllShops,
        SumMaxMarkAllShops
    )
RETURN
IF( SELECTEDVALUE(Marks[country]) = "Country 1" && SELECTEDVALUE(Marks[evaluated item]) = "Phone Skills",
    IF( ISINSCOPE(Marks[subitem]),
        StandardAverage,
        AllShopsAverage
    ),
    StandardAverage
)

Note use of indentation to make it more readable and also use of VAR.
Some things about VAR:

• it's a constant not a variable
• StandardAverage is calculated once not twice (which it would be if the DIVIDE formula was repeated). 
• it makes the code easier to read

[Anonymous · Accepted solution]

Hi @AnthNC ,

Thanks for the reply from @belvoir99 , please allow me to provide another insight: 

You can change the expression for ScoreBelvoir99 to

ScoreBelvoir99 =
VAR SumMark = SUM(Marks[weighted mark])
VAR SumMaxMark = SUM(Marks[max weighted mark])
VAR SumMarkAllShops =
CALCULATE(
SUM(Marks[weighted mark]),
REMOVEFILTERS(Marks[shop])
)
VAR SumMaxMarkAllShops =
CALCULATE(
SUM(Marks[max weighted mark]),
REMOVEFILTERS(Marks[shop])
)
RETURN
IF(ISINSCOPE(Marks[subitem]),
DIVIDE(
SumMark.
SumMaxMark
), IF
IF(ISINSCOPE(Marks[evaluated item]) && SELECTEDVALUE(Marks[country])="Country 1" && SELECTEDVALUE(Marks[evaluated item])="Phone skills",
DIVIDE(
SumMarkAllShops,
SumMaxMarkAllShops
), DIVIDE(
DIVIDE(
SumMark, SumMaxMarkAllShops ), DIVIDE(
SumMaxMark
)
)
)

You can also change Score3 to

Score3 =
VAR SumMark = SUM(Marks[weighted mark])
VAR SumMaxMark = SUM(Marks[max weighted mark])
VAR SumMarkAllShops =
CALCULATE(
SUM(Marks[weighted mark]),
REMOVEFILTERS(Marks[shop])
)
VAR SumMaxMarkAllShops =
CALCULATE(
SUM(Marks[max weighted mark]),
REMOVEFILTERS(Marks[shop]))
VAR IsScopeSpecific = SELECTEDVALUE(Marks[country])="Country 1" && SELECTEDVALUE(Marks[evaluated item])="Phone skills" && NOT ISINSCOPE(Marks [subitem])
RETURN
IF(IsScopeSpecific.
DIVIDE(SumMarkAllShops, SumMaxMarkAllShops, SumMarkAllShops)
SumMaxMarkAllShops
),
DIVIDE(
SumMark, SumMaxMarkAllShops ), DIVIDE(
SumMaxMark
)
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[AnthNC]

Thank you very much for your reply v-kaiyue-msft !

[belvoir99]

You're almost there! Try changing the IF(ISINSCOPE( line to:

IF( ISINSCOPE(Marks[evaluated item]) && Marks[country] = "Country 1" && Marks[evaluated item] = "Phone Skills",
	DIVIDE(

This should then use your 'all shops' calculation only for when the country is Country 1 and the evaluated item is Phone skills. Everything else will calculate in the normal way.

If that doesn't work and you want me to look at the pbix, then post a shared link to OneDrive, Dropbox, Box etc.

[AnthNC]

Hi belvoir99,

Apparently the ISINSCOPE fx doesn't take a specific variables from a column; i can't input ISINSCOPE(Marks[country])= "Country 1" for example.

I used this formula with ISINSCOPE :

Score2 = 
VAR SumMark = SUM(Marks[weighted mark])
VAR SumMaxMark = SUM(Marks[max weighted mark])
VAR SumMarkAllShops = 
	CALCULATE(
		SUM(Marks[weighted mark]),
		REMOVEFILTERS(Marks[shop])
	)
VAR SumMaxMarkAllShops = 
	CALCULATE(
		SUM(Marks[max weighted mark]),
		REMOVEFILTERS(Marks[shop]))

VAR IsScopeSpecific = ISINSCOPE(Marks[country])= "Country 1" && ISINSCOPE(Marks[evaluated item])="Phone skills"

RETURN
IF(IsScopeSpecific,
	DIVIDE(SumMarkAllShops,
		SumMaxMarkAllShops
	),
	DIVIDE(
		SumMark,
		SumMaxMark
	)
)

I got this error message :

So I tried SELECTEDVALUE instead :

Score3 = 
VAR SumMark = SUM(Marks[weighted mark])
VAR SumMaxMark = SUM(Marks[max weighted mark])
VAR SumMarkAllShops = 
	CALCULATE(
		SUM(Marks[weighted mark]),
		REMOVEFILTERS(Marks[shop])
	)
VAR SumMaxMarkAllShops = 
	CALCULATE(
		SUM(Marks[max weighted mark]),
		REMOVEFILTERS(Marks[shop]))

VAR IsScopeSpecific = SELECTEDVALUE(Marks[country])= "Country 1" && SELECTEDVALUE(Marks[evaluated item])="Phone skills"

RETURN
IF(IsScopeSpecific,
	DIVIDE(SumMarkAllShops,
		SumMaxMarkAllShops
	),
	DIVIDE(
		SumMark,
		SumMaxMark
	)
)

It's very close to what i'm looking for but i don't want the AVERAGE score to apply on a subitem level :

This is what i'm looking for :

Here's the link https://1drv.ms/u/s!Agk0pOMpwO_IoTnnNVVBRWcFyWLi?e=gYts1X 

Thanks again

[belvoir99 · Accepted solution]

Thanks for the PBIX file.

You are correct re ISINSCOPE. If you look at my code carefully you will see that there is a bracket after the column name e.g.

IF( ISINSCOPE(Marks[evaluated item]),

ISINSCOPE takes a column name as an argument i.e. Marks[evaluated item] and then returns a True or False value therefore your code returns an data type error as it is comparing a True/False value with a string value 'Country 1':

VAR IsScopeSpecific = ISINSCOPE(Marks[country])= "Country 1"

It looks like, in Score3, we have the correct calculation at the 'Phone skills' level but, at the 'evaluated item' level we want to revert back to the standard average.
I've tweaked my DAX measure a little bit - it's probably similar to or identical to Clara Gong's @Anonymous calculation - and now returns the correct calculation:

ScoreBelvoir99 = 
VAR SumMark = SUM(Marks[weighted mark])
VAR SumMaxMark = SUM(Marks[max weighted mark])
VAR SumMarkAllShops = 
	CALCULATE(
		SUM(Marks[weighted mark]),
		REMOVEFILTERS(Marks[shop])
	)
VAR SumMaxMarkAllShops = 
	CALCULATE(
		SUM(Marks[max weighted mark]),
		REMOVEFILTERS(Marks[shop])
	)
VAR StandardAverage =
 	DIVIDE(
		SumMark,
		SumMaxMark
	)
VAR AllShopsAverage =
    DIVIDE(
        SumMarkAllShops,
        SumMaxMarkAllShops
    )
RETURN
IF( SELECTEDVALUE(Marks[country]) = "Country 1" && SELECTEDVALUE(Marks[evaluated item]) = "Phone Skills",
    IF( ISINSCOPE(Marks[subitem]),
        StandardAverage,
        AllShopsAverage
    ),
    StandardAverage
)

Note use of indentation to make it more readable and also use of VAR.
Some things about VAR:

• it's a constant not a variable
• StandardAverage is calculated once not twice (which it would be if the DIVIDE formula was repeated). 
• it makes the code easier to read

[AnthNC]

Many thanks!

Sorry again for not uploading the pbix file earlier. I know how to do it now 🙂

I will definitely take into account your recommandations for my next publication on this forum.

[belvoir99 · Accepted solution]

Glad I could help and that you got there. I too have learnt some stuff about ISINSCOPE and hierarchies.

If you want, you can give me a 'thumbs up' and/or Accept as Solution!

[belvoir99]

So my understanding is you want to show the AVERAGE of all shops at the [evaluated item] level but below that level just the AVERAGE for each shop x subitem combination.

You might want to try out ISINSCOPE function - see - https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/ - to switch calculation according to the hierarchical level

e.g.

Score = 
VAR SumMark = SUM(Marks[weighted mark])
VAR SumMaxMark = SUM(Marks[max weighted mark])
VAR SumMarkAllShops = 
	CALCULATE(
		SUM(Marks[weighted mark]),
		REMOVEFILTERS(Marks[shop])
	)
VAR SumMaxMarkAllShops = 
	CALCULATE(
		SUM(Marks[max weighted mark]),
		REMOVEFILTERS(Marks[shop])
	)

RETURN
IF( ISINSCOPE(Marks[evaluated item]),
	DIVIDE(
		SumMarkAllShops,
		SumMaxMarkAllShops
	),
	DIVIDE(
		SumMark,
		SumMaxMark
	),
)

  I can't test this without some data but hopefully it might give you a way forward.

[AnthNC]

Thanks belvoir99!

Here are the results :

Here's what i'm trying to do only for Country 1 and the evaluated item "Phone skills" (not subitems) for Shops 1, 2 and 3:

The logic i'm trying to formulate is :

If ISINSCOPE([country]="Country 1") AND ISINSCOPE([evaluated item]="Phone skills")

Then AVERAGE the scores of Shop 1, 2 and 3 for the evaluated item "Phone skills"

Else CALCULATE normal scores

Would you know how to translate that in DAX ?

I tried to put a download link to the pbix but it says "spam" on my message

How can i upload the pbix file ?

Thank you