calculate trend

[griffinst]

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

[v-tejrama]

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.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Tejaswi.
Community Support Team.

[v-tejrama]

Hi @griffinst,

Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Tejaswi.

[v-tejrama]

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.

Please don’t forget to “Accept as Solution” and Give “Kudos” if the response was helpful.

Thank you.

Tejaswi.

[v-tejrama]

Hi @griffinst,

I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.

If your question has been answered, please “Accept as Solution” and Give “Kudos” so others with similar issues can easily find the resolution.

Thank you.

Tejaswi.

[Ashish_Excel]

Hi,

PBI file here.

Hope this helps.

[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 
_result

I used your sample data and created one Matrix visual like below,

Thanks 

Mason 

[Irwan]

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 visual

as 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.