How to add Average Column in a Matrix

[Syndicate_Admin]

Good afternoon.

I need help to see how to include in a matrix an average column that calculates on each record.

I's passing an example to be more specific.

This would be the end result we want to achieve:

We build a matrix where we have as columns the months of the year and as a record the names of the metricas that we want to show are shown. Metrica 1, and in the log shows its values per month, and so with all metricas.

What we need to achieve is to generate the same thing that power bi does to generate the total column, but showing an average of each metric x the number of months shown.

I hope the consultation is understood.

Thank you!

[Syndicate_Admin]

Hello. Thank you for the prompt response. I think this is not the solution I was looking for anyway.

The model tables involved in this report are as follows:
FACT_TABLE

TIME_TABLE

DesdeTime Table to Fact table 1:N

Later. when we assemble the Matrix to display the data we formatter it to show the name of the metricas as a record. (in the fact table the metricas are columns)

And we get them to show like this:

Because power bi automatically generates a totalizer at the record level, we need to be able to also include an Average per record.

I'm new to the world of Power Bi and really any suggestions you make to me will help me.

Thank you

[Anonymous]

Hi @BDIAZCUSI ,

If so, please select these Metrica columns and UNPIVOT them (as Figure 1) in Power Query Editor, so they will be split into Attribute and Value columns as shown in Figure 2 below.

UNPIVOT Metrias columns

Then create the following measures similar to the ones in my previous post. Please find the attachment for all details.

Sum of value = SUM('FACT_TABLE'[Value])​

Measure =
IF (
ISINSCOPE ( 'TIME_TABLE'[YEAR_MONTH] ),
SUMX (
VALUES ( 'FACT_TABLE'[Metricas] ),
SUMX ( VALUES ( 'TIME_TABLE'[YEAR_MONTH] ), [Sum of value] )
),
AVERAGEX (
VALUES ( 'FACT_TABLE'[Metricas] ),
AVERAGEX ( VALUES ( 'TIME_TABLE'[YEAR_MONTH] ), [Sum of value] )
)
)

Best Regards

[Syndicate_Admin]

Thank you very much @Syndicate_Admin.

Unfortunately this solution will not be able to apply, since this report contains several sheets that use the model, and contains several calculated metrics and calculated columns.

While unpivot is a useful solution, it affects all the calculated metrics I have (these also need to show this AVG that I want to achieve), since I can no longer use them as columns to calculate, and it also affects the rest of the reports involved. (It was a fact I forget to include, sorry).

You may need to deliver this solution by showing the averages in another table in the report and calculating them individually.

Thank you for your patience.

Best regards!

[Anonymous]

Hi @BDIAZCUSI ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a measure to get the sum of value

Sum of value = SUM('Table'[Value])

Measure = 
IF (
    ISINSCOPE ( 'Table'[Date] ),
    SUMX ( GROUPBY ( 'Table', 'Table'[Metrica], 'Table'[Date] ), [Sum of value] ),
    AVERAGEX (
        GROUPBY ( 'Table', 'Table'[Metrica], 'Table'[Date] ),
        [Sum of value]
    )
)

Best Regards