Forum Discussion
Calculating Average over Multiple Columns
Hi JimKeelan ,
I was able to get the correct output by using a calculated column instead of a measure, as you suggested.
use a Calulated column :Average = VAR NonZeroValues = SELECTCOLUMNS( FILTER( { (Sheet1[LockupLME]), (Sheet1[Lockup2]), (Sheet1[Lockup3]), (Sheet1[Lockup4]), (Sheet1[Lockup5]), (Sheet1[Lockup6]), (Sheet1[Lockup7]), (Sheet1[Lockup8]), (Sheet1[Lockup9]), (Sheet1[Lockup10]), (Sheet1[Lockup11]), (Sheet1[Lockup12]) }, [Value] <> 0 ), "Val", [Value] ) RETURN ROUND(AVERAGEX(NonZeroValues, [Val]), 0)
the expected output :If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
Thank you!!
Thank you for your suggestion, however I think because my lockup measures are calculated (see snip below), I can't select them in the code you provided. Would you expect this to happen? I'm new to writing DAX and still have a lot to learn. Thanks
Hi JimKeelan ,
I was able to get the correct output by using a calculated column instead of a measure, as you suggested.
use a Calulated column :
Average =
VAR NonZeroValues =
SELECTCOLUMNS(
FILTER(
{
(Sheet1[LockupLME]),
(Sheet1[Lockup2]),
(Sheet1[Lockup3]),
(Sheet1[Lockup4]),
(Sheet1[Lockup5]),
(Sheet1[Lockup6]),
(Sheet1[Lockup7]),
(Sheet1[Lockup8]),
(Sheet1[Lockup9]),
(Sheet1[Lockup10]),
(Sheet1[Lockup11]),
(Sheet1[Lockup12])
},
[Value] <> 0
),
"Val", [Value]
)
RETURN
ROUND(AVERAGEX(NonZeroValues, [Val]), 0)
the expected output :
If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
Thank you!!