Forum Discussion
Add columns to Matrix (Last year total and difference)
Hello,
I want to create a Matrix as:
I need to add Total for Last Year and Diffrence to the Matrix as two extra columns. So far, I created the follwing table and masure:
Hi , what you need to do is to not calculate [Total_Paid_LY] measure at a level of a single month.
---
Total_Paid_LY ADJUSTED =
VAR _SelectedMonth = SELECTEDVALUE( c_Calendar[Month-Year] ) //currenly selected month - should always be 1 at month-year level (except Total)
VAR _SingleMonthFiltered = ISFILTERED( c_Calendar[Month-Year] ) //still show values in case when only 1 month is filtered (e.g. Jan-24)
RETURN
IF(
_SelectedMonth = BLANK() || _SingleMonthFiltered,
YOUR CALCULATION FOR MEASURE Total_Paid_LY,
BLANK()
)
---_SelectedMonth = BLANK() will be blank only when multiple months are selected at the same time, which is a case of "Total".
You'd still need to manually adjust Grand Total column (because all visible "totals" are actually subtotals). You can easily do it by turning off auto-size width:
The only issue with this approach is when a single month is filtered/selected. Consider Jan-25, for current year you'll have only 1 month and then total. You might still want to show only totals for LY and difference. So far I din't find the way to make it possible because when only 1 month is selected, that month is absolutely equal to subtotal. To show the value, I'm using _SingleMonthFiltered variable.
The very last step is to adjust result for difference, because you want to calculate it only when Total_Paid_LY ADJUSTED is not blank:
---
DisplayesValue =
SWITCH(
SELECTEDVALUE(
MeasureType[MeasureType]) ,
"Current Year", [Total_Paid] ,
"Last Year", [Total_Paid_LY ADJUSTED] ,
"Difference",
IF(
[Total_Paid_LY ADJUSTED] = BLANK(),
BLANK(),
[Total_Paid] - [Total_Paid_LY ADJUSTED] ),
BLANK()
)
---I hope you'll find it useful! Good luck with your project 🙂
2 Replies
- Sergii24
Super User
Hi , what you need to do is to not calculate [Total_Paid_LY] measure at a level of a single month.
---
Total_Paid_LY ADJUSTED =
VAR _SelectedMonth = SELECTEDVALUE( c_Calendar[Month-Year] ) //currenly selected month - should always be 1 at month-year level (except Total)
VAR _SingleMonthFiltered = ISFILTERED( c_Calendar[Month-Year] ) //still show values in case when only 1 month is filtered (e.g. Jan-24)
RETURN
IF(
_SelectedMonth = BLANK() || _SingleMonthFiltered,
YOUR CALCULATION FOR MEASURE Total_Paid_LY,
BLANK()
)
---_SelectedMonth = BLANK() will be blank only when multiple months are selected at the same time, which is a case of "Total".
You'd still need to manually adjust Grand Total column (because all visible "totals" are actually subtotals). You can easily do it by turning off auto-size width:
The only issue with this approach is when a single month is filtered/selected. Consider Jan-25, for current year you'll have only 1 month and then total. You might still want to show only totals for LY and difference. So far I din't find the way to make it possible because when only 1 month is selected, that month is absolutely equal to subtotal. To show the value, I'm using _SingleMonthFiltered variable.
The very last step is to adjust result for difference, because you want to calculate it only when Total_Paid_LY ADJUSTED is not blank:
---
DisplayesValue =
SWITCH(
SELECTEDVALUE(
MeasureType[MeasureType]) ,
"Current Year", [Total_Paid] ,
"Last Year", [Total_Paid_LY ADJUSTED] ,
"Difference",
IF(
[Total_Paid_LY ADJUSTED] = BLANK(),
BLANK(),
[Total_Paid] - [Total_Paid_LY ADJUSTED] ),
BLANK()
)
---I hope you'll find it useful! Good luck with your project 🙂