Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Percent Change Between Matrix

I have this matrix that I would like to add a third column with the percent change between the two categories. For instance, the first value for U-16191 would be about a -17.99% decrease. How do I accomphish this? 

 

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , Anonymous 

    Try steps as below:

    Create measure "measure value" to instead  the field  "value" :

    Measure  value =
VAR _inital_Request =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Category] = "Initial Request" )
    )
VAR _MPSC_Approved =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Category] = "MPSC Approved" )
    )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Category] ),
        SUM ( 'Table'[Value] ),
        _MPSC_Approved / _inital_Request - 1
    )

      

    Change the column subtotals label from total to"Percentchange"

     

    Here is a demo .

    pbix attach 

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Ryan,

     

    Try to follow the steps below

     

    Create a new column inside power bi with the formula below

    Percentage change = (MPSC Approved / Initial Request) - 1

     

    Then click on the column and click on modeling to change the format to percentage. 

     

    And finally, add this new column to your matrix. 

     

    🙂

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    I believe you want:

     

    Measure =
  VAR __Sum1 = SUMX(FILTER('Table',[Category] = "Initial Request"),[Value])
  VAR __Sum2 = SUMX(FILTER('Table',[Category] = "MSPC Approved"),[Value])
RETURN
(__Sum1 - __Sum2) / __Sum1

     

    Now, you may run into the issue where this measure ends up getting added multiple times. You can get around that by using something like this:

    https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/td-p/963588

     

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    Hi , Anonymous 

    Try steps as below:

    Create measure "measure value" to instead  the field  "value" :

    Measure  value =
VAR _inital_Request =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Category] = "Initial Request" )
    )
VAR _MPSC_Approved =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Category] = "MPSC Approved" )
    )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Category] ),
        SUM ( 'Table'[Value] ),
        _MPSC_Approved / _inital_Request - 1
    )

      

    Change the column subtotals label from total to"Percentchange"

     

    Here is a demo .

    pbix attach 

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.