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 var...
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

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

      • hyousuf9090's avatar
        hyousuf9090
        Regular Visitor
        6 years ago

        hi thanks, what about profit% delta?