DAX average score

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.

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

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!