Forum Discussion

Peavey's avatar
Peavey
Helper III
6 years ago
Solved

Matrix visual - add column for average

Hi,   Is there a way to add a column in a matrix with average values in addition to totals?   Thanks,  /A
Need Help
Tips and Tricks
  • Icey's avatar
    Icey
    6 years ago

    Hi Peavey ,

     

    It means that you could change the default Total value in Matrix visual to any aggregation value you want. For example, change Total to Avg.

    Measure =
IF (
    HASONEFILTER ( 'Table'[Index] ),
    SUM ( 'Table'[Value] ),
    AVERAGE ( 'Table'[Value] )
)

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Icey's avatar
    Icey
    6 years ago

    Hi Peavey ,

     

    Try this:

    Measure =
IF (
    HASONEFILTER ( 'Table'[Index] ),
    SUM ( 'Table'[Value] ),
    AVERAGEX (
        SUMMARIZE (
            'Table',
            'Table'[Asset],
            'Table'[Index],
            "Sum_", SUM ( 'Table'[Value] )
        ),
        [Sum_]
    )
)

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Peavey's avatar
Peavey
Helper III
6 years ago

Thanks... but I really don't understand what they are doing here?

Are they making a new measure in a column?

 

/A

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Peavey ,

     

    It means that you could change the default Total value in Matrix visual to any aggregation value you want. For example, change Total to Avg.

    Measure =
IF (
    HASONEFILTER ( 'Table'[Index] ),
    SUM ( 'Table'[Value] ),
    AVERAGE ( 'Table'[Value] )
)

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Peavey's avatar
      Peavey
      Helper III
      6 years ago

      Thanks, it works perfectly 🙂

       

      Can you explain the formula for me please? 

      I don't follow what happens here: 

      IF (
    HASONEFILTER ( 'Table'[Index] ),
    SUM ( 'Table'[Value] ),
    AVERAGE ( 'Table'[Value] )
)

        

       

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi Peavey ,

         

        HASONEFILTER returns TRUE when the number of directly filtered values on columnName is one; otherwise returns FALSE. And HASONEVALUE could also be used in your scenario. Here is one post which explains the principle in more detail. 

        In your scenario, one column is one filtered value. Total column in Matrix visaul contains more than one filtered values. Hope I explained clearly.

         

         

        Best Regards,

        Icey