Forum Discussion

griffinst's avatar
griffinst
Icon for Helper I rankHelper I
1 year ago
Solved

calculate 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 bel...
Need Help
  • Irwan's avatar
    Irwan
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.

         

        Thank you.

        Tejaswi.