Ok, here are two possible ways of going about this.
The first is using the default matrix capabilities; this method will have a better performance but the metrics' headers are less clear.
The second involves creating a specific table layout which recreates the measures displayed to be used as the matrix header. It provides more clarity at the expense of performance possibly (my sample dataset is minute so I can't really compare the performances of both solutions).
So...
Option 1: Default matrix capabilities.
We need two tables for "Year" to be able to compare the values. One of these tables has a regular One-to-many active relationship with the fact table; the second Year table's relationship is inactive. The model looks like this:
The measures for each metric follow these patterns:
For the first Year selection.
Client Billings =
SUM(FTable[Client_Billings])
For the comparison Year selection:
Comp Billings =
CALCULATE (
[Client Billings],
REMOVEFILTERS ( DYear[Year] ),
USERELATIONSHIP ( VYear[Year], FTable[Year] )
)
and the % var between both years:
Billings % var =
SWITCH (
TRUE (),
AND ( ISBLANK ( [Client Billings] ), ISBLANK ( [Comp Billings] ) ), BLANK (),
AND ( [Client Billings] = 0, [Comp Billings] = 0 ), 0,
DIVIDE ( [Client Billings], [Comp Billings] ) - 1
)
The matrix is set up as follows, with each individual measure set in order in the values bucket:
To get:
Option 2: Custom Header to show the columns by years
The setup requires creating a custom table to use as the matrix header. Firstly we need a list of the measures and their respective order. (copy and paste from Excel or use the "Enter Data" option to create it):
We then need to add the Years from the model (Crossjoin) to create both the actual header needed in the matrix and a sorting column. This is all done in Power Query:
Leave this table unrelated in the model. The model looks like this:
We only need to create one measure, referencing the measures we have already created, which is the one we will use for the matrix:
For Matrix =
SWITCH (
TRUE (),
ISBLANK ( [Client Billings] ) && ISBLANK ( [Comp Billings] )
&& ISBLANK ( [Net Revenue] ), BLANK (),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
MAX ( Header[Order] ) = 1
), FORMAT ( [Client Billings], "Currency" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( VYear[Year] ),
MAX ( Header[Order] ) = 2
), FORMAT ( [Comp Billings], "Currency" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
MAX ( Header[Order] ) = 3
), FORMAT ( [Billings % var], "Percent" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
MAX ( Header[Order] ) = 4
), FORMAT ( [Gross Revenue], "Currency" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( VYear[Year] ),
MAX ( Header[Order] ) = 5
), FORMAT ( [Comp Gross Revenue], "Currency" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
MAX ( Header[Order] ) = 6
), FORMAT ( [Gross Rev % Var], "Percent" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
MAX ( Header[Order] ) = 7
), FORMAT ( [Net Revenue], "Currency" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( VYear[Year] ),
MAX ( Header[Order] ) = 8
), FORMAT ( [Comp Net Revenue], "Currency" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
MAX ( Header[Order] ) = 9
), FORMAT ( [Net Rev % Var], "Percent" )
)
We then set up the Matrix with the field fromt the Header Table as the columns, whatever dimension for the rows and the [For Matrix] measure as the values:
So take your pick!
I've attached the sample PBIX file
Ok, here are two possible ways of going about this.
The first is using the default matrix capabilities; this method will have a better performance but the metrics' headers are less clear.
The second involves creating a specific table layout which recreates the measures displayed to be used as the matrix header. It provides more clarity at the expense of performance possibly (my sample dataset is minute so I can't really compare the performances of both solutions).
So...
Option 1: Default matrix capabilities.
We need two tables for "Year" to be able to compare the values. One of these tables has a regular One-to-many active relationship with the fact table; the second Year table's relationship is inactive. The model looks like this:
The measures for each metric follow these patterns:
For the first Year selection.
Client Billings =
SUM(FTable[Client_Billings])
For the comparison Year selection:
Comp Billings =
CALCULATE (
[Client Billings],
REMOVEFILTERS ( DYear[Year] ),
USERELATIONSHIP ( VYear[Year], FTable[Year] )
)
and the % var between both years:
Billings % var =
SWITCH (
TRUE (),
AND ( ISBLANK ( [Client Billings] ), ISBLANK ( [Comp Billings] ) ), BLANK (),
AND ( [Client Billings] = 0, [Comp Billings] = 0 ), 0,
DIVIDE ( [Client Billings], [Comp Billings] ) - 1
)
The matrix is set up as follows, with each individual measure set in order in the values bucket:
To get:
Option 2: Custom Header to show the columns by years
The setup requires creating a custom table to use as the matrix header. Firstly we need a list of the measures and their respective order. (copy and paste from Excel or use the "Enter Data" option to create it):
We then need to add the Years from the model (Crossjoin) to create both the actual header needed in the matrix and a sorting column. This is all done in Power Query:
Leave this table unrelated in the model. The model looks like this:
We only need to create one measure, referencing the measures we have already created, which is the one we will use for the matrix:
For Matrix =
SWITCH (
TRUE (),
ISBLANK ( [Client Billings] ) && ISBLANK ( [Comp Billings] )
&& ISBLANK ( [Net Revenue] ), BLANK (),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
MAX ( Header[Order] ) = 1
), FORMAT ( [Client Billings], "Currency" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( VYear[Year] ),
MAX ( Header[Order] ) = 2
), FORMAT ( [Comp Billings], "Currency" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
MAX ( Header[Order] ) = 3
), FORMAT ( [Billings % var], "Percent" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
MAX ( Header[Order] ) = 4
), FORMAT ( [Gross Revenue], "Currency" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( VYear[Year] ),
MAX ( Header[Order] ) = 5
), FORMAT ( [Comp Gross Revenue], "Currency" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
MAX ( Header[Order] ) = 6
), FORMAT ( [Gross Rev % Var], "Percent" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
MAX ( Header[Order] ) = 7
), FORMAT ( [Net Revenue], "Currency" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( VYear[Year] ),
MAX ( Header[Order] ) = 8
), FORMAT ( [Comp Net Revenue], "Currency" ),
AND (
MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
MAX ( Header[Order] ) = 9
), FORMAT ( [Net Rev % Var], "Percent" )
)
We then set up the Matrix with the field fromt the Header Table as the columns, whatever dimension for the rows and the [For Matrix] measure as the values:
So take your pick!
I've attached the sample PBIX file
Helpful resources
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Power BI Monthly Update - May 2024
Check out the May 2024 Power BI update to learn about new features.
Europe’s largest Microsoft Fabric Community Conference
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
