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
- v-tejrama
Community Support
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.
