Consolidate Measures in one Table

[Anonymous]

Hello. I have several measures for my data in Power BI that goes like this:

Subject A:
Measure 1: average score for 3 months
Measure 2: average score for 6 months
Measure 3: average score for 1 year

Subjects B and C have the same measures.

Can I create a table/matrix where there will be columns:
Average score for 3 months
Average score for 6 months
Average score for 1 year

And my rows will be the subjects?

Thank you in advance.

[v-shex-msft · Accepted solution]

Hi @Anonymous,

I'd like to suggest you to union your tables and keep their table name.

Combine =
UNION (
    ADDCOLUMNS ( 'Subject A', "Table Name", "Subject A" ),
    ADDCOLUMNS ( 'Subject B', "Table Name", "Subject B" ),
    ADDCOLUMNS ( 'Subject C', "Table Name", "Subject C" )
)

Then write measure with condition to check their own table names.

meaure =
VAR currDate =
    MAX ( Combine[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Combine[Amount] ),
        FILTER ( ALLSELECTED ( Combine ), 'date range filter formula' ),
        VALUES ( Combine[Table Name] )
    )

Please use your date range filter formula to replace bold part.

Regards,
Xiaoxin Sheng