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.

Irwan's avatar
Irwan
Icon for Super User rankSuper User
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.