Matrix: How to display some values per year & month and other values per year in one table

[MSchepers]

Hi,

I am trying to create a Matrix containing several values, which has both years and months in the columns. However, I want some of the values to be displayed only on a yearly level, while other values are displayed per month.

So like this:

The measures are calculated in multiple layers (and the value and title of Value D is dynamic based on a user selection).

I have searched several forums but could not find a way to achieve this. Does anyone know a way?

[v-frfei-msft]

Hi @MSchepers ，

ISINSCOPE should work in your secnario. Here I created a measure as below.

Measure = 
IF ( ISINSCOPE ( 'Table'[date].[Month] ), BLANK (), SUM ( 'Table'[value2] ) )

[MSchepers]

Hi @v-frfei-msft 

I tried your solution, almost there... But it doesn't yet quite give what I am looking for. I want to have the months in the columns, but only display the measures which are not blank. Now it displays also the yearly measures, which are blank in each month except for the yearly total.

Any idea how to get rid of this?

Current result in PBI 

While I am looking for something like this:

[MSchepers]

Thanks a lot, that promises to be a good solution! Will try after holiday to see if it works out.

[parry2k]

@MSchepers What will be logic where to see by year and where by month?

[MSchepers]

Hi @parry2k , it depends on the measure being displayed in the matrix value. So some measures are by definition only per year, while others are broken down per month (and should be aggregated per year by going one level up in Matrix column hierarchy)