Forum Discussion
Variance Matrix based off slicer selection
Hello jkendalljones
Hope you are doing well!I understand the issue regarding matrix variance .I’ve found a solution that meets your requirements. Here are the modified steps :
1.First, create a Date table that will represent the months you want to use for comparison. This Date table will be used by the slicer to allow the user to select different months.
DAX FORMULA
DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2020, 1, 1), DATE(2025, 12, 31)), -- Adjust dates as needed
"MonthYear", FORMAT([Date], "MMM YYYY"),
"Year", YEAR([Date]),
"Month", MONTH([Date]))
2.Now create two duplicate date tables Duplicate the DateTable twice and name them.
- DateTable1
- DateTable2
3.Create inactive relationships between these tables and the Balance Sheet table:
- DateTable1[Date] → Balance Sheet[Date]
- DateTable2[Date] → Balance Sheet[Date]
4.Active Relationship for Variance Matrix: Keep the active relationship between the main DateTable and the Balance Sheet[Date].
5. Add Slicers for Date Selection(Place two slicers on the page):
- Slicer 1: DateTable1[MonthYear] (for Matrix 1).
- Slicer 2: DateTable2[MonthYear] (for Matrix 2).
6. Create a DAX Measures for Matrix1 , Matrix2 and Variance Matrix individually.
1. Measure for Matrix 1
Calculate the total for Matrix 1 based on DateTable1 slicer:
DAX
Total Matrix 1 =
CALCULATE(
SUM('Balance Sheet'[Total]),
USERELATIONSHIP('Balance Sheet'[Date], 'DateTable1'[Date]))
2. Measure for Matrix 2
Calculate the total for Matrix 2 based on DateTable2 slicer:
DAX
Total Matrix 2 =
CALCULATE(
SUM('Balance Sheet'[Total]),
USERELATIONSHIP('Balance Sheet'[Date], 'DateTable2'[Date]))
3. Variance Measure
Calculate the variance between the two selected months:
Variance Amount =
[Total Matrix 1] - [Total Matrix 2]
7.Set Up Visuals
- Matrix 1 (First Month)[Rows: Category ,Values: Total Matrix1].
- Matrix 2(Second Month)[Rows: Category,Values: Total Matrix 2].
- Matrix 3 (Variance Matrix)[Rows: Category,Values: Variance Amount].
8.Step 6: Test Interactions
- Go to Format → Edit Interactions to ensure:
- Slicer 1 only affects Matrix 1.
- Slicer 2 only affects Matrix 2.
- Neither slicer directly affects the variance matrix (it updates dynamically through the measures).
Please check the pictures below:
I hope you will get the solution as per the requirements you mentioned above.If you’re still experiencing issues, feel free to reach out to us for further assistance!
If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.
I sincerely appreciate the thorough response!
Unfortunately, my main issue is that I need the 3rd variance matrix to be on the same powerbi page as the other two matrix's, and I need those two primary matrix's to be displaying the dates selected by the user in the slicer(s).
Example using your 1st screenshot: I would need Matrix 1 to be displaying April 2023 values, Matrix 2 to be displaying July 2024 values, and matrix 3 to be displaying the variance between the two selected MonthYears all on the same page.
Hello jkendalljones
Hope you are doing well!
I understand the issue regarding matrix variance .I’ve found a solution that meets your requirements. Here are the modified steps :
1.First, create a Date table that will represent the months you want to use for comparison. This Date table will be used by the slicer to allow the user to select different months.
DAX FORMULA
DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2020, 1, 1), DATE(2025, 12, 31)), -- Adjust dates as needed
"MonthYear", FORMAT([Date], "MMM YYYY"),
"Year", YEAR([Date]),
"Month", MONTH([Date]))
2.Now create two duplicate date tables Duplicate the DateTable twice and name them.
- DateTable1
- DateTable2
3.Create inactive relationships between these tables and the Balance Sheet table:
- DateTable1[Date] → Balance Sheet[Date]
- DateTable2[Date] → Balance Sheet[Date]
4.Active Relationship for Variance Matrix: Keep the active relationship between the main DateTable and the Balance Sheet[Date].
5. Add Slicers for Date Selection(Place two slicers on the page):
- Slicer 1: DateTable1[MonthYear] (for Matrix 1).
- Slicer 2: DateTable2[MonthYear] (for Matrix 2).
6. Create a DAX Measures for Matrix1 , Matrix2 and Variance Matrix individually.
1. Measure for Matrix 1
Calculate the total for Matrix 1 based on DateTable1 slicer:
DAX
Total Matrix 1 =
CALCULATE(
SUM('Balance Sheet'[Total]),
USERELATIONSHIP('Balance Sheet'[Date], 'DateTable1'[Date]))
2. Measure for Matrix 2
Calculate the total for Matrix 2 based on DateTable2 slicer:
DAX
Total Matrix 2 =
CALCULATE(
SUM('Balance Sheet'[Total]),
USERELATIONSHIP('Balance Sheet'[Date], 'DateTable2'[Date]))
3. Variance Measure
Calculate the variance between the two selected months:
Variance Amount =
[Total Matrix 1] - [Total Matrix 2]
7.Set Up Visuals
- Matrix 1 (First Month)[Rows: Category ,Values: Total Matrix1].
- Matrix 2(Second Month)[Rows: Category,Values: Total Matrix 2].
- Matrix 3 (Variance Matrix)[Rows: Category,Values: Variance Amount].
8.Step 6: Test Interactions
- Go to Format → Edit Interactions to ensure:
- Slicer 1 only affects Matrix 1.
- Slicer 2 only affects Matrix 2.
- Neither slicer directly affects the variance matrix (it updates dynamically through the measures).
Please check the pictures below:
I hope you will get the solution as per the requirements you mentioned above.If you’re still experiencing issues, feel free to reach out to us for further assistance!
If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.
Hello,
Thank you so much for your assistance! I followed every step very closely and double checked everything and for some reason my variance matrix is not updating dynamically based off the slicer selections - it just says 0 in every category.
I attached a few screenshots in case you can see I am doing anything incorrectly - but I am pretty certain I followed all of the steps.
Hi jkendalljones
Hope you are doing well!
As I was going through your last message, I was unable to find the mistake in the screenshots provided by you. As the steps done by you are correct and still you are unable to get the solution, I would like to request you to share your PBIX file so that I can cross verify and get a better understanding of all the steps done by you and this way I will be able to give you a better solution.Hello,
I decided to try all of your steps again with a brand new file and it worked perfectly as intended!! Thank you SO SO much for your help - I really appreciate it!