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

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