Matrix visual - add column for average

[Peavey]

Hi,

Is there a way to add a column in a matrix with average values in addition to totals?

Thanks, 

/A

[Icey · Accepted solution]

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.

[amitchandak]

@Peavey , I do not think so. But can change it Avg. Refer, if this can help

https://community.powerbi.com/t5/Desktop/AVG-instead-Total-in-matrix/td-p/327031

[Peavey]

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

Are they making a new measure in a column?

/A

[Icey · Accepted solution]

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]

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]

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 · Accepted solution]

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]

Hi @Icey ,

Could you send me the sample file? 

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

Thanks

[Icey]

Hi @Peavey ,

The basic file is the same as that uploaded before, and the measure is changed.

Best Regards,

Icey

[Icey]

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]

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

Why SUM IF=TRUE and AVERAGE IF=FALSE?

Thanks alot! 🙂