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

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



     

8 Replies

  • BhavinVyas3003's avatar
    BhavinVyas3003
    Super User
    1 year ago

    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]
    )

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



         

  • techies's avatar
    techies
    Super User
    1 year ago

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

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

        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!