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

  

 

Peavey's avatar
Peavey
Helper III
6 years ago

As it appears, it did not give me the needed solution 😞

The top visual has the average values (haven't changed the name Total to avg). 

The bottom visual has the original values.

 

The case is in column 2: There are two values here 1000 and 500. 

I need the values in the matrix to sum, and the last column to be average of the sum values in the matrix. I don't want the average of ALL values.

If that was understandable?

  • Icey's avatar
    Icey
    Community Support
    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

      Hi Icey ,

       

      Could you send me the sample file? 

      I didn't understand what 'Table'[Value] is?

       

      Thanks