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

  

 

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

  • Peavey's avatar
    Peavey
    Helper III
    6 years ago

    Is it to avoid calculating the empty values into the average?

    Why SUM IF=TRUE and AVERAGE IF=FALSE?

     

    Thanks alot! 🙂