calculations between custom tables

[hyousuf9090]

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!!!

[amitchandak]

@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]

hi, thanks for helping.

The sample data is:

Year	Geography	Revenue	Profit
2010	North America	100	70
2010	Latin America	80	60
2010	Asia	50	30
2010	Europe	30	15
2010	Africa	20	7
2011	North America	120	95
2011	Latin America	150	100
2011	Asia	180	120
2011	Europe	50	30
2011	Africa	30	15
2012	North America	135	80
2012	Latin America	120	85
2012	Asia	80	50
2012	Europe	40	25
2012	Africa	30	20
2013	North America	125	100
2013	Latin America	100	75
2013	Asia	95	90
2013	Europe	85	50
2013	Africa	50	30

The two custom tables are:

using filter on year: 2010

Geography	Revenue	Profit	Profit%
Africa	20	7	35%
Asia	50	30	60%
Europe	30	15	50%
Latin America	80	60	75%
North America	100	70	70%
Total	280	182	65%

using filter on year: 2012

Geography	Revenue	Profit	Profit%
Africa	30	20	67%
Asia	80	50	63%
Europe	40	25	63%
Latin America	120	85	71%
North America	135	80	59%
Total	405	260	64%

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 · Accepted solution]

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]

hi thanks, what about profit% delta?

[v-deddai1-msft]

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

[v-deddai1-msft]

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

Best Regards,

Dedmon Dai