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,

 

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.