Forum Discussion
Helper Icalculate trend
I have a matrix in Power BI. I need to add a trend calculation to the matrix. I'm not sure how to do this and whether to use a measure or a visual calculation. Basically there should be a trend below each category showing the percentage using this formula..
2023 value/2022 value -1
2024 value/2023 value -1
| Row Labels | 2022 | 2023 | 2024 |
| Category 1 | $5,837.66 | $5,550.75 | $4,812.64 |
| Category 2 | $6,998.28 | $5,257.36 | $6,520.18 |
| Category 3 | $9,063.49 | $8,163.55 | $7,560.28 |
This is what I want it to look like...
| Row Labels | 2022 | 2023 | 2024 |
| Category 1 | $5,837.66 | $5,550.75 | $4,812.64 |
| trend | -4.91% | -13.30% | |
| Category 2 | $6,998.28 | $5,257.36 | $6,520.18 |
| trend | -24.88% | 24.02% | |
| Category 3 | $9,063.49 | $8,163.55 | $7,560.28 |
| trend | -9.93% | -7.39% |
here is some sample data
| Category | yr | Cost |
| Category 1 | 2022 | $5,837.66 |
| Category 1 | 2023 | $5,550.75 |
| Category 1 | 2024 | $4,812.64 |
| Category 2 | 2022 | $6,998.28 |
| Category 2 | 2023 | $5,257.36 |
| Category 2 | 2024 | $6,520.18 |
| Category 3 | 2022 | $9,063.49 |
| Category 3 | 2023 | $8,163.55 |
| Category 3 | 2024 | $7,560.28 |
hello griffinst
please check if this accomodate your need.
first thing first, you need a column for in matrix rows but [Trend] looks like a measure.
therefor you need to tick "Switch Values to Row" option so you can use measure as rows in matrix visual.
i wrote this DAX for [Trend] measure.
Trend =
var _PreviousYear = MAX('Table'[yr])-1
var _PreviousCost =
CALCULATE(
MAX('Table'[Cost]),
'Table'[yr]=_PreviousYear
)
var _CurrentCost = MAX('Table'[Cost])
var _MinYear =
MINX(
ALL('Table'),
'Table'[yr]
)
var _Percent =
DIVIDE(
_CurrentCost,
_PreviousCost
)-1
Return
IF(
SELECTEDVALUE('Table'[yr])=_MinYear,
0,
_Percent
)Next, plot all column and measure in matrix visualas you can see above, eventhough 'Sum of Cost' and [Trend] are placed in Values, but they are shown as rows in matrix visual.
Hope this will help.
Thank you.
7 Replies
IrwanSuper User
hello griffinst
please check if this accomodate your need.
first thing first, you need a column for in matrix rows but [Trend] looks like a measure.
therefor you need to tick "Switch Values to Row" option so you can use measure as rows in matrix visual.
i wrote this DAX for [Trend] measure.
Trend =
var _PreviousYear = MAX('Table'[yr])-1
var _PreviousCost =
CALCULATE(
MAX('Table'[Cost]),
'Table'[yr]=_PreviousYear
)
var _CurrentCost = MAX('Table'[Cost])
var _MinYear =
MINX(
ALL('Table'),
'Table'[yr]
)
var _Percent =
DIVIDE(
_CurrentCost,
_PreviousCost
)-1
Return
IF(
SELECTEDVALUE('Table'[yr])=_MinYear,
0,
_Percent
)Next, plot all column and measure in matrix visualas you can see above, eventhough 'Sum of Cost' and [Trend] are placed in Values, but they are shown as rows in matrix visual.
Hope this will help.
Thank you.
- MasonMA
Hi griffinst:
Showing 'Trend' below every 'Category' may involve more calculations like UNION multiple Calculated Tables. You can have AI generate codes and give it a try.
However, I would suggest create one simple 'Trend' calculated measure and have it shown beside 'Cost', which looks slightly different but give same results.
YoY Trend % = VAR CurrentYear = SELECTEDVALUE(CostData[yr]) VAR PrevYear = CurrentYear - 1 VAR CurrValue = CALCULATE([Total Cost], CostData[yr] = CurrentYear) VAR PrevValue = CALCULATE([Total Cost], CostData[yr] = PrevYear) VAR _result= IF( NOT ISBLANK(PrevValue), DIVIDE(CurrValue, PrevValue) - 1 ) RETURN _resultI used your sample data and created one Matrix visual like below,
Thanks
Mason
Ashish_ExcelSolution Supplier
Hi griffinst ,
To create a matrix that shows both Cost and YoY Trend % side by side for each category and year, I followed this approach: First, I created a calculated table RowLabels using UNION to generate two rows per category one with RowType = Cost and another with RowType =Trend:
RowLabels =
UNION (
SELECTCOLUMNS ( VALUES ( 'YourData'[Category] ), "Category", [Category], "RowType", "Cost" ),
SELECTCOLUMNS ( VALUES ( 'YourData'[Category] ), "Category", [Category], "RowType", "Trend" )
)
Then I added a RowKey column to both tables:
RowLabels[RowKey] = RowLabels[Category] & "-" & RowLabels[RowType]
Data[RowKey] = Data[Category] & "-Cost"
Next, I created a Many-to-One relationship from Data[RowKey] to RowLabels[RowKey].For the trend calculation, I used:
Trend % =
VAR SelectedYear = SELECTEDVALUE ( 'YourData'[yr] )
VAR PrevYear = SelectedYear - 1
VAR ThisYearCost = CALCULATE ( SUM ( 'YourData'[Cost] ), 'YourData'[yr] = SelectedYear )
VAR LastYearCost = CALCULATE ( SUM ( 'YourData'[Cost] ), 'YourData'[yr] = PrevYear )
RETURN IF ( NOT ISBLANK (LastYearCost), DIVIDE ( ThisYearCost - LastYearCost, LastYearCost ) )
Finally, I created a measure to dynamically switch between Cost and Trend:DisplayValue =
VAR RowType = SELECTEDVALUE ( RowLabels[RowType] )
RETURN SWITCH ( RowType, "Cost", SUM ( 'YourData'[Cost] ), "Trend", [Trend %] )Please find the attached pbix file for youe reference.
Best Regards,
Tejaswi.
Community Support Team.Hi griffinst,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query.
Thank you.Tejaswi.
Hi griffinst,
Just checking in have you been able to resolve this issue? If so, it would be greatly appreciated if you could mark the most helpful reply accordingly. This helps other community members quickly find relevant solutions.
Thank you.
Tejaswi.
