Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello.
I have a main table and a date table. The main table has sales by 2 companies. The date table has fiscal months and related to the main table through the DATE column.
MAIN TABLE
DATE | CUSTOMER | SALES | DEPT. |
Nov. 1, 2021 | ABC | 25 | A |
Nov. 2, 2021 | XZY | 50 | B |
Dec. 5, 2022 | ABC | 35 | A |
Using Matrix table, this is the result that I am getting
CUSTOMER | DEPT. A | DEPT. A | DEPT. B | DEPT. B | TOTAL (LY) | TOTAL (CY) |
NOV. (LY) | NOV. (CY) | NOV. (LY) | NOV. (CY) | |||
CUST. ABC | 200 | 100 | 500 | 300 | 700 | 400 |
However, I would like to present the month to month comparison between current year (CY) and last year (LY) so that the matrix table will show like this.
CUSTOMER | DEPT. A | DEPT. B | TOTAL (LY) | DEPT. A | DEPT. B | TOTAL (CY) | DIFFERENCE (CY)-(LY) |
(LY) NOV | (LY) NOV | (CY) NOV | (CY) NOV | ||||
CUST. ABC | 200 | 500 | 700 | 100 | 300 | 400 | -300 |
Here is the table and matrix illustration:
Any help is highly appreciated. Thanks.
Solved! Go to Solution.
@Oros , the Calculation group can help measure needs to be above
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0
Here is one way. First the model:
Create a new table to use for the columns in the matrix following this pattern:
Custom Matrix =
VAR _Dept =
ADDCOLUMNS (
VALUES ( 'Department Table'[DEPT.] ),
"Index", RANK.EQ ( 'Department Table'[DEPT.], 'Department Table'[DEPT.], ASC )
)
VAR _Rows =
DISTINCTCOUNT ( 'Department Table'[DEPT.] )
VAR _Total = { ( "Total", _Rows + 1 ) }
VAR _DT =
UNION ( _Dept, _Total )
VAR _Metrics =
{
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), "MMM" ) & " (LY)",
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), "MMM" ) & " (CY)"
}
VAR _A =
CROSSJOIN ( _DT, _Metrics )
VAR _Diff =
{ ( "Difference (CY)-(LY)", _Rows + 2, BLANK () ) }
RETURN
ADDCOLUMNS (
UNION ( _A, _Diff ),
"Period",
SWITCH (
TRUE (),
CONTAINSSTRING ( [Value], "LY" ), 1,
CONTAINSSTRING ( [Value], "CY" ), 2,
3
)
)
Next the measures, starting with a simple sum measure for the sales:
Sales CY =
CALCULATE (
[Sum Sales],
FILTER (
ALL ( 'Date Table' ),
'Date Table'[Year] = YEAR ( TODAY () )
&& 'Date Table'[MonthNum] = MONTH ( TODAY () )
)
)
Sales PY =
CALCULATE (
[Sum Sales],
FILTER (
ALL ( 'Date Table' ),
'Date Table'[Year]
= YEAR ( TODAY () ) - 1
&& 'Date Table'[MonthNum] = MONTH ( TODAY () )
)
)
and the final measure to use in the matrix:
Measure for Custom Matrix =
VAR _CY = CALCULATE([Sales CY], TREATAS(VALUES('Custom Matrix'[DEPT.]), 'Department Table'[DEPT.]))
VAR _LY = CALCULATE([Sales PY], TREATAS(VALUES('Custom Matrix'[DEPT.]), 'Department Table'[DEPT.]))
VAR _DIFF = [Sales CY] - [Sales PY]
VAR _Rows = COUNT('Department Table'[DEPT.])
RETURN
SWITCH(TRUE(),
AND(MAX('Custom Matrix'[Index]) = _Rows +1, MAX('Custom Matrix'[Period]) = 1), [Sales PY],
AND(MAX('Custom Matrix'[Index]) = _Rows +1, MAX('Custom Matrix'[Period]) = 2), [Sales CY],
SELECTEDVALUE('Custom Matrix'[Period]) = 1, _LY,
SELECTEDVALUE('Custom Matrix'[Period]) = 2, _CY,
SELECTEDVALUE('Custom Matrix'[Period]) = 3, _DIFF)
Now create the matrix with the customer field as rows, the fields from the Custom Matrix table as columns and the [Measure for Custom Matrix] as values. Turn off the column subtotals and you get:
Sample PBIX file attached
Proud to be a Super User!
Paul on Linkedin.
Here is one way. First the model:
Create a new table to use for the columns in the matrix following this pattern:
Custom Matrix =
VAR _Dept =
ADDCOLUMNS (
VALUES ( 'Department Table'[DEPT.] ),
"Index", RANK.EQ ( 'Department Table'[DEPT.], 'Department Table'[DEPT.], ASC )
)
VAR _Rows =
DISTINCTCOUNT ( 'Department Table'[DEPT.] )
VAR _Total = { ( "Total", _Rows + 1 ) }
VAR _DT =
UNION ( _Dept, _Total )
VAR _Metrics =
{
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), "MMM" ) & " (LY)",
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), "MMM" ) & " (CY)"
}
VAR _A =
CROSSJOIN ( _DT, _Metrics )
VAR _Diff =
{ ( "Difference (CY)-(LY)", _Rows + 2, BLANK () ) }
RETURN
ADDCOLUMNS (
UNION ( _A, _Diff ),
"Period",
SWITCH (
TRUE (),
CONTAINSSTRING ( [Value], "LY" ), 1,
CONTAINSSTRING ( [Value], "CY" ), 2,
3
)
)
Next the measures, starting with a simple sum measure for the sales:
Sales CY =
CALCULATE (
[Sum Sales],
FILTER (
ALL ( 'Date Table' ),
'Date Table'[Year] = YEAR ( TODAY () )
&& 'Date Table'[MonthNum] = MONTH ( TODAY () )
)
)
Sales PY =
CALCULATE (
[Sum Sales],
FILTER (
ALL ( 'Date Table' ),
'Date Table'[Year]
= YEAR ( TODAY () ) - 1
&& 'Date Table'[MonthNum] = MONTH ( TODAY () )
)
)
and the final measure to use in the matrix:
Measure for Custom Matrix =
VAR _CY = CALCULATE([Sales CY], TREATAS(VALUES('Custom Matrix'[DEPT.]), 'Department Table'[DEPT.]))
VAR _LY = CALCULATE([Sales PY], TREATAS(VALUES('Custom Matrix'[DEPT.]), 'Department Table'[DEPT.]))
VAR _DIFF = [Sales CY] - [Sales PY]
VAR _Rows = COUNT('Department Table'[DEPT.])
RETURN
SWITCH(TRUE(),
AND(MAX('Custom Matrix'[Index]) = _Rows +1, MAX('Custom Matrix'[Period]) = 1), [Sales PY],
AND(MAX('Custom Matrix'[Index]) = _Rows +1, MAX('Custom Matrix'[Period]) = 2), [Sales CY],
SELECTEDVALUE('Custom Matrix'[Period]) = 1, _LY,
SELECTEDVALUE('Custom Matrix'[Period]) = 2, _CY,
SELECTEDVALUE('Custom Matrix'[Period]) = 3, _DIFF)
Now create the matrix with the customer field as rows, the fields from the Custom Matrix table as columns and the [Measure for Custom Matrix] as values. Turn off the column subtotals and you get:
Sample PBIX file attached
Proud to be a Super User!
Paul on Linkedin.
@Oros , the Calculation group can help measure needs to be above
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0
User | Count |
---|---|
92 | |
87 | |
77 | |
73 | |
68 |
User | Count |
---|---|
116 | |
107 | |
88 | |
65 | |
63 |