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

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?

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

      Hi hyousuf9090 ,

       

      Just add two more measure based on hyousuf9090 's reply:

       

       

      profit%1 = CALCULATE(SUM(Geography[Profit])/SUM(Geography[Revenue]),KEEPFILTERS(TREATAS(VALUES(Table1[Year]),Geography[Year])))

profit%2 = CALCULATE(SUM(Geography[Profit])/SUM(Geography[Revenue]),KEEPFILTERS(TREATAS(VALUES(Table2[Year]),Geography[Year])))

       

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      Best Regards,

      Dedmon Dai