Forum Discussion
How to create a matrix with calculated values based on a selection of years from a slicer?
Hi, TomasGazsi
Thanks for reaching out to the Microsoft fabric community forum.The reason for this issue is that in the matrix, you have set the column to year, so your output results include a year filter. This causes the variable filter in the measure to be empty. In your table visual object, there is no year, so the output is normal. My suggestion is to create an unrelated date table to use as the rows and columns in the matrix, and replace the values part with measures:
1.First, create a calculated table, then add it to the matrix, using the year from the calculated table as a slicer:
Table = CALENDAR(DATE(2023, 01, 01), DATE(2026, 12, 31))2.Then, use the following two measures as values:
Normal total = CALCULATE ( SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ), FILTER ( ALLSELECTED ( Tabl_kalendar_NEW ), YEAR ( 'Tabl_kalendar_NEW'[Date] ) = MAX ( 'Table'[Date].[Year] ) && MONTH ( 'Tabl_kalendar_NEW'[Date] ) = SWITCH ( MAX ( 'Table'[Date].[Month] ), "January", 1, "February", 2, "March", 3, "April", 4, "May", 5, "June", 6, "July", 7, "August", 8, "September", 9, "October", 10, "November", 11, "December", 12 ) ) )diff = VAR mid1 = MINX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ) VAR mad1 = MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ) VAR mid2 = CALCULATE ( SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ), FILTER ( ALLSELECTED ( Tabl_kalendar_NEW ), YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mid1 ) && MONTH ( 'Tabl_kalendar_NEW'[Date] ) = SWITCH ( MAX ( 'Table'[Date].[Month] ), "January", 1, "February", 2, "March", 3, "April", 4, "May", 5, "June", 6, "July", 7, "August", 8, "September", 9, "October", 10, "November", 11, "December", 12 ) ) ) VAR mad2 = CALCULATE ( SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ), FILTER ( ALLSELECTED ( Tabl_kalendar_NEW ), YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mad1 ) && MONTH ( 'Tabl_kalendar_NEW'[Date] ) = SWITCH ( MAX ( 'Table'[Date].[Month] ), "January", 1, "February", 2, "March", 3, "April", 4, "May", 5, "June", 6, "July", 7, "August", 8, "September", 9, "October", 10, "November", 11, "December", 12 ) ) ) VAR count1 = CALCULATE ( DISTINCTCOUNT ( 'Table'[Date].[Year] ), ALLSELECTED ( 'Table' ) ) VAR f = IF ( count1 = 2, mad2 - mid2, BLANK () ) RETURN f3.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous And I see that the TOTAL row is average, but can I change it to SUM the whole year? Thanks
Hi, TomasGazsi
Thank you for your prompt response and for accepting my idea as the solution. Regarding your question, you just need to add an additional check using ISINSCOPE(). I have modified the measure as follows:
diff =
VAR mid1 =
MINX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mad1 =
MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
VAR mid2 =
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mid1 )
&& MONTH ( 'Tabl_kalendar_NEW'[Date] )
= SWITCH (
MAX ( 'Table'[Date].[Month] ),
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
)
)
VAR mad2 =
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mad1 )
&& MONTH ( 'Tabl_kalendar_NEW'[Date] )
= SWITCH (
MAX ( 'Table'[Date].[Month] ),
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
)
)
VAR count1 =
CALCULATE ( DISTINCTCOUNT ( 'Table'[Date].[Year] ), ALLSELECTED ( 'Table' ) )
RETURN
IF (
count1 = 2,
IF (
ISINSCOPE ( 'Table'[Date].[Month] ),
mad2 - mid2,
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mad1 )
)
)
- CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = YEAR ( mid1 )
)
)
),
BLANK ()
)
Normal total =
VAR ff =
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = MAX ( 'Table'[Date].[Year] )
&& MONTH ( 'Tabl_kalendar_NEW'[Date] )
= SWITCH (
MAX ( 'Table'[Date].[Month] ),
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
)
)
RETURN
IF (
ISINSCOPE ( 'Table'[Date].[Month] ),
ff,
CALCULATE (
SUM ( Tabl_kalendar_NEW[TotalWithoutVAT] ),
FILTER (
ALLSELECTED ( Tabl_kalendar_NEW ),
YEAR ( 'Tabl_kalendar_NEW'[Date] ) = MAX ( 'Table'[Date].[Year] )
)
)
)
Here is the current result:
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks again Anonymous , this solution helped!