Forum Discussion
How to create a matrix with calculated values based on a selection of years from a slicer?
Hi,
I create a "total operating result" report for the customer by individual months and years.
I have gone through several tutorials that addressed the issue at hand but have not achieved a satisfactory result.
To simplify the assignment, I created sample data, which I will use to explain the needs.
- I have generated a test data table, which contains only 2 columns, which are Date and TotalWithoutVAT. Each month has 2 records. It is generated from the year 2023 to the year 2026.
- I have generated a table Tabl_kalendar_NEW. Tabl_kalendar_NEW = CALENDAR(DATE(2023,01,01),DATE(2026,12,31))
- I added columns to it (I don't know if it's necessary):
Year: Year = YEAR(Tabl_kalendar_NEW[Date])
Month: Month = FORMAT(Tabl_kalendar_NEW[Date], "MMMM")
MonthNum: MonthNum = MONTH(Tabl_kalendar_NEW[Date]) - I sorted the table by the YEAR column.
- Then I linked / connected the table Tabl_kalendar_NEW with test_table
- Then I started creating the measures and matrix.
Measures:
SumPriceWithoutVAT Year1 = CALCULATE([SumPriceWithoutVAT], CalendarTable_NEW[Date].[Year] = MIN(CalendarTable_NEW[Date].[Year]))
SumPriceWithoutVAT Year2 = CALCULATE([SumPriceWithoutVAT], CalendarTable_NEW[Date].[Year] = MAX(CalendarTable_NEW[Date].[Year]))
Interannual Difference_V3 =
VAR Year1 = CALCULATE( [Sum_PriceWithoutVAT],
FILTER(
ALL(Table_calendar_NEW),
Tabl_kalendar_NEW[YEAR] = MIN(Tabl_kalendar_NEW[YEAR])
)
)
VAR Year2 = CALCULATE( [Sum_PriceWithoutVAT],
FILTER(
ALL(Table_calendar_NEW),
Tabl_kalendar_NEW[YEAR] = MAX(Tabl_kalendar_NEW[YEAR])
)
)
RETURN
Year2 - Year1
- Alternative difference calculations (Measures):
Interannual Difference =
VAR SelectedYears = VALUES(Calendar_Table_NEW[YEAR])
VAR Year1 = MINX(SelectedYears, Calendar_Table_NEW[YEAR]) -- Select year 1 by slicer
VAR Year2 = MAXX(SelectedYears, Calendar_Table_NEW[YEAR] ) -- Select year 2 by slicer
VAR TotalYear1 =
CALCULATE([SumPriceWithoutVAT], 'Calendar_Table_NEW'[Year] = Year1)
VAR TotalYear2 =
CALCULATE([SumPriceWithoutVAT], 'Calendar_Table_NEW'[Year] = Year2)
RETURN
SumYear2 - SumYear1calculated_year-to-year_difference_in_total_V2 = [SumPriceWithoutVAT Year2] - [SumPriceWithoutVAT Year1]
- Matrix: the problem is that the differences keep returning 0. But the bottom table (not the matrix) calculates it correctly. I can't identify where I might be making a mistake. Or is it necessary to set up the matrix specifically? Or is the problem in the connection/settings of the calendar table and the test table?
Matrix legend:
Rows - months
Columns - years
Values:
Column 1 - SumPriceWithoutVAT Year1
Column 2 - SumPriceWithoutVAT Year2
Column 3 - Interannual Difference_V3
Column 4 - calculated_year-to-year_difference_in_total_V2
Column 5 - Interannual Difference - Setting up the bottom table:
Here you can see that selecting years from the slicer works. Also the sum of prices excluding VAT works. And also the calculation of the difference between Year2 and Year1 works.
Could you please advise me where I might be making a mistake? Why doesn't the matrix behave "similarly" to the table - considering the selection of years from the slicer?
I would be very grateful for any advice on how I can do calculations while the user selects the year according to his/her own wishes
Thanks a lot Tom.
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.
5 Replies
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.
Anonymous Thank you so much!!
It helped me solve my problem that I had been struggling with for several days.
