Forum Discussion

Fcoatis's avatar
Fcoatis
Post Patron
9 years ago
Solved

% Change in a non contiguous Matrix

I have this matrix with measure Tot Gross = sum(INVEST[Gross Bal]) . How do I calculate the percentual difference of Tot Gross (being calendar non contiguous due to holidays and weekends)?

 

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Fcoatis,

    You can use a measure, please review the following solution.

    I have sample data table.



    Add index column in Edit Query-> Close & Apply. You will get the following table.



    Create a calculated column using the formula.

    Last = LOOKUPVALUE(Table9[value],Table9[Index.1],Table9[Index.1]-1)




    Finally, create a measure. For unique date, sum(Table9[value]) equals itself. Create a table visual used to display the result.

    Percentage1 = (SUM(Table9[value])-SUM(Table9[Last]))/SUM(Table9[Last])



    If you have other issues, please let me know.

    Best Regards,
    Angelia



4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi Fcoatis,

    What's the mean of the percentual difference of Tot Gross, could you please post an example? 

    The difference includes increase and decrease between the two numbers you are comparing.

    Increase = (New Number - Original Number)÷ Original Number × 100.

    Decrease = (Original Number - New Number)÷ Original Number × 100.

    In your requirement, what's the New Number and Original Number?

    Best Regards,
    Angelia

    • Fcoatis's avatar
      Fcoatis
      Post Patron
      9 years ago

      Hi Angelina,

       

      I'm looking for a measure that calculates (actual value/previous value)-1

       

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi Fcoatis,

        You can use a measure, please review the following solution.

        I have sample data table.



        Add index column in Edit Query-> Close & Apply. You will get the following table.



        Create a calculated column using the formula.

        Last = LOOKUPVALUE(Table9[value],Table9[Index.1],Table9[Index.1]-1)




        Finally, create a measure. For unique date, sum(Table9[value]) equals itself. Create a table visual used to display the result.

        Percentage1 = (SUM(Table9[value])-SUM(Table9[Last]))/SUM(Table9[Last])



        If you have other issues, please let me know.

        Best Regards,
        Angelia