Forum Discussion

JimKeelan's avatar
JimKeelan
New Member
1 year ago
Solved

Calculating Average over Multiple Columns

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 ...
Need Help
  • v-aatheeque's avatar
    v-aatheeque
    1 year ago

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



     

pankajnamekar25's avatar
pankajnamekar25
Super User
1 year ago

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's avatar
JimKeelan
New Member
1 year ago

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's avatar
    v-aatheeque
    Community Support
    1 year ago

    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's avatar
      v-aatheeque
      Community Support
      1 year ago

      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.