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!!
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!!
Hi JimKeelan ,
If the information is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.
We value your feedback, and it will help us to assist others who might have a similar query. Thank you for your contribution in enhancing Microsoft Fabric Community Forum.