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

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