Calculating Average over Multiple Columns

[JimKeelan]

Good morning

I have a table in PBI which I have recreated below.  I am trying to create a new column with the average of data of the 12 columns Lockup LME through to Lockup12 but, I want to exclude the columns where the value is 0. 

So, for Partner 1 I need to calculate the average of columns LockupLME, Lockup2, Lockup3, Lockup4, Lockup5, Lockup6, Lockup7.

For Partner 2, I need to calculate the average of columns LockupLME, Lockup2, Lockup3, Lockup4, Lockup5, Lockup6.

For Partner 3 & 4, I would calculate for all 12 columns.

I have added a column at the end of the table in the snip named 'Desired Outcome', this is the average total I need to display.

Thank you in advance for any help you may be able to offer.

James

[techies]

Hi @JimKeelan in case you prefer power query editor

here is the custom column

let
values = {
[LockupLME], [Lockup2], [Lockup3], [Lockup4], [Lockup5], [Lockup6],
[Lockup7], [Lockup8], [Lockup9], [Lockup10], [Lockup11], [Lockup12]
},
nonZero = List.Select(values, each _ <> 0),
average = if List.Count(nonZero) = 0 then null else Number.Round(List.Average(nonZero), 0)
in
average

[v-aatheeque]

Hi @JimKeelan ,

I wanted to follow up regarding your request for calculating the average of the Lockup columns while excluding any columns with a value of 0.If you encounter any issues or have further questions about implementing this, please let me know. Additionally, if there have been any recent changes to your model or data sources, sharing those details could help us troubleshoot more effectively.

If your issue has been resolved, I would appreciate it if you could mark the helpful response as Accepted, as it may assist others facing similar challenges.

Thank you for contributing to the Microsoft Fabric Community.

[v-aatheeque]

Hi @JimKeelan 

Just following up on your question about  calculate the average of columns.

If you're still exploring options or running into any isuues, feel free to share more details we’d be happy to assist further or suggest possible workarounds.

If your issue has been resolved? it would be great if you could update the thread. If any of the earlier replies helped, please consider marking it as the Accepted Answer so others in the community can benefit as well.

Thanks again for engaging with the Microsoft Fabric Community!

[pankajnamekar25]

Hello @JimKeelan 

try this DAX for calculated column

Desired Outcome =

VAR ValuesList = {

    [LockupLME], [Lockup2], [Lockup3], [Lockup4], [Lockup5], [Lockup6],

    [Lockup7], [Lockup8], [Lockup9], [Lockup10], [Lockup11], [Lockup12]

}

VAR NonZeroValues =

    FILTER (

        ADDCOLUMNS (

            GENERATESERIES (1, 12),

            "Value", SWITCH (

                [Value],

                1, [LockupLME],

                2, [Lockup2],

                3, [Lockup3],

                4, [Lockup4],

                5, [Lockup5],

                6, [Lockup6],

                7, [Lockup7],

                8, [Lockup8],

                9, [Lockup9],

                10, [Lockup10],

                11, [Lockup11],

                12, [Lockup12]

            )

        ),

        [Value] <> 0

    )

RETURN

    AVERAGEX (NonZeroValues, [Value])

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[JimKeelan]

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 

[v-aatheeque · Accepted solution]

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!!

[v-aatheeque]

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.

[BhavinVyas3003]

please try this DAX to create calculated column,

AverageNonZeroLockups =
AVERAGEX(
FILTER(
{
[LockupLME], [Lockup2], [Lockup3], [Lockup4], [Lockup5], [Lockup6],
[Lockup7], [Lockup8], [Lockup9], [Lockup10], [Lockup11], [Lockup12]
},
[Value] <> 0
),
[Value]
)