Show Measure Only in Totals in Matrix

Hi AP1081 ,

Sorry for the late response but I missed your questions on the notifications.

You can try the following:

• Create a table that merges the Year/Month and a category column with the measure name
• For the measure you want to have in the Year month you must add a value for each of the combinations year month measure for the ones you want in the total you need to add only a row

I used this calculated table:

Category =
UNION (
    SUMMARIZE (
        'Fact_Table',
        'Fact_Table'[Year],
        'Fact_Table'[Month],
        "Measure", "Measure A"
    ),
    ROW ( "Year", "Total", "Month", "Total", "Measure", "Measure B" ),
    ROW ( "Year", "Total", "Month", "Total", "Measure", "Measure C" )
)

Final result:

Be aware that my model is not a complex one but you can use the values in the calendar table for years and month if you have it.

Now create the following  measures:

Calculation = VAR _Year = FILTER(
			SELECTCOLUMNS(
				FILTER(
					Category,
					Category[Year] <> "Total"
				),
				"Year", VALUE(Category[Year])
			),
			[Year] IN VALUES('Fact_Table'[Year])
		)
		VAR _Month = FILTER(
			SELECTCOLUMNS(
				FILTER(
					Category,
					Category[Year] <> "Total"
				),
				"Month", VALUE(Category[Month])
			),
			[Month] IN VALUES('Fact_Table'[Month])
		)

		VAR _measureA = CALCULATE(
			[Measure A],
			'Fact_Table'[Year] IN _Year,
			'Fact_Table'[Month] IN _Month
		)

		RETURN

			SWITCH(
				SELECTEDVALUE(Category[Measure]),
				"Measure A", _measureA,
				"Measure B", [Measure B],
				"Measure C", [Measure C]
			)


Format = SWITCH(
			TRUE(),
			SELECTEDVALUE(Category[Measure]) = "Measure B" && [Calculation] < 15, "Red",
			SELECTEDVALUE(Category[Measure]) = "Measure B" && [Calculation] < 20, "Yellow",
			SELECTEDVALUE(Category[Measure]) = "Measure B" && [Calculation] >= 20, "Green"
		)

The first measure makes the calculation based on the level of the hierarchy you are, in the first variables Year and Month I did the filter in order for your matrix to also be responsive to any date filter you may have.

Now you just need to setup the matrix:

• Columns:
  • Category[Year]
  • Category[Month]
  • Category[Measure]
• Rows
  • FactTabler[Cat]
• Values
  • [Calculation]
• Condittional formatting
  • [Format]

If you want you can include several other values on the formatting or on the calculation just by changing the switch statment.

Please see file attach.