Forum Discussion

hyousuf9090's avatar
hyousuf9090
Regular Visitor
6 years ago
Solved

calculations between custom tables

hi I have no idea how to solve this please help!!

 

 

so I have data (which in its simplified form is) Geography, Revenue, Profit and Profit%. There are 5 geographies and values in other three variables are for 10 Years from 2010 to 2019. 

 

I want to create 3 matrices or tables. First is the period under analysis, second is the period being compared and third is the delta between two periods. 

 

The first two tables are easy I just use slicer on tables; one can select the period for analysis and comparison - easy. The problem is to calculate the third table with deltas of revenue, profit and profit% from first two tables (which themselves are variable and depend on slicer).

 

Is there any solution that exists??? Highly appreciate any help!!!

Need Help
Tutorial Requests
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Here is one way to solve this one:

     

    1.  Make two disconnected tables with the Year values using - 

    Year1 = VALUES(Geography[Year])
    Year2 = VALUES(Geography[Year])
    2. Make two slicers, one for each of the above (Year1[Year], Year2[Year])
    3. Make a table with your Year and Geography columns.
    4. Make these measures:
    Revenue Year1 = CALCULATE(SUM(Geography[Revenue]), KEEPFILTERS(TREATAS(VALUES(Year1[Year]), Geography[Year])))
    Revenue Year2 = CALCULATE(SUM(Geography[Revenue]), KEEPFILTERS(TREATAS(VALUES(Year2[Year]), Geography[Year])))
    Profit Year 1 = CALCULATE(SUM(Geography[Profit]), KEEPFILTERS(TREATAS(VALUES(Year1[Year]), Geography[Year])))
    Profit Year 2 = CALCULATE(SUM(Geography[Profit]), KEEPFILTERS(TREATAS(VALUES(Year2[Year]), Geography[Year])))
    5. Add the Year 1 measures to the table
    6. Duplicate the table, and replace with the Year 2 measures
    7. Make your delta table with the Geography column and these two measures
    Delta Revenue = [Revenue Year1]-[Revenue Year2]
    Delta Profit = [Profit Year 1]-[Profit Year 2]
     
    This should be the result
     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

6 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi hyousuf9090 ,

     

    You need to create three line and clustered column charts for each deltas of revenue, profit and profit% . Use the line to show the difference between two variables .Really can't use table or matrix to show the difference between two variables.

     

    You should rank the table by  Geography  or group them by  Geography  then add index column for it and expand it.

     

    Then you can use the following measure to show the deltas of revenue, profit and profit% :

     

    Measure = ABS((SUMX(FILTER(Table,Table[rank] = MIN(Table[rank])),Table[Revenue])-SUMX(FILTER(Table,Table[rank] = MAX(Table[rank])),Table[Revenue])))

     

    You can refer to the similar post: https://community.powerbi.com/t5/Desktop/Dynamically-calculate-differences-based-on-slicer-slection/td-p/38979

     

    Best Regards,

    Dedmon Dai

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    hyousuf9090 , you should be able to analyze data across a common dimension and should able to take diff also.

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    • hyousuf9090's avatar
      hyousuf9090
      Regular Visitor
      6 years ago

      hi, thanks for helping.

      The sample data is:

       

      YearGeographyRevenueProfit
      2010North America10070
      2010Latin America8060
      2010Asia5030
      2010Europe3015
      2010Africa207
      2011North America12095
      2011Latin America150100
      2011Asia180120
      2011Europe5030
      2011Africa3015
      2012North America13580
      2012Latin America12085
      2012Asia8050
      2012Europe4025
      2012Africa3020
      2013North America125100
      2013Latin America10075
      2013Asia9590
      2013Europe8550
      2013Africa5030

       

      The two custom tables are:

      using filter on year: 2010

      GeographyRevenueProfitProfit%
      Africa20735%
      Asia503060%
      Europe301550%
      Latin America806075%
      North America1007070%
      Total28018265%

       

      using filter on year: 2012

      GeographyRevenueProfitProfit%
      Africa302067%
      Asia805063%
      Europe402563%
      Latin America1208571%
      North America1358059%
      Total40526064%

       

      needed: a table of delta of values from the above two tables that updates the values as filters are changed in above two tables.

       

      thanks!

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        Here is one way to solve this one:

         

        1.  Make two disconnected tables with the Year values using - 

        Year1 = VALUES(Geography[Year])
        Year2 = VALUES(Geography[Year])
        2. Make two slicers, one for each of the above (Year1[Year], Year2[Year])
        3. Make a table with your Year and Geography columns.
        4. Make these measures:
        Revenue Year1 = CALCULATE(SUM(Geography[Revenue]), KEEPFILTERS(TREATAS(VALUES(Year1[Year]), Geography[Year])))
        Revenue Year2 = CALCULATE(SUM(Geography[Revenue]), KEEPFILTERS(TREATAS(VALUES(Year2[Year]), Geography[Year])))
        Profit Year 1 = CALCULATE(SUM(Geography[Profit]), KEEPFILTERS(TREATAS(VALUES(Year1[Year]), Geography[Year])))
        Profit Year 2 = CALCULATE(SUM(Geography[Profit]), KEEPFILTERS(TREATAS(VALUES(Year2[Year]), Geography[Year])))
        5. Add the Year 1 measures to the table
        6. Duplicate the table, and replace with the Year 2 measures
        7. Make your delta table with the Geography column and these two measures
        Delta Revenue = [Revenue Year1]-[Revenue Year2]
        Delta Profit = [Profit Year 1]-[Profit Year 2]
         
        This should be the result
         

        If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat