Connecting Two Tables to form one matrix table

[milkywaypowerbi]

I have two tables.

One showing the family -> product ->colour level with revenue and cost details

The other table is the investment cost with details at family level.

I tried creating an investment id = family & year to form linkages but it is not working in powerbi.

The investment row is adding all the numbers up and not reacting to the family level slicer.

What I hope to achieve:

Here are the links to my excel and powerbi files.

https://drive.google.com/drive/folders/1T8L_o5lbAXIa-Hkh6eDDx0Iejb4i_yYT?usp=sharing

Thank you so much.

[VahidDM]

Hi @milkywaypowerbi 

Try to add an Investment column to your first table "family -> product ->colour" by using the lookup DAX code, then use all columns from one table to create your Matrix/Table.
Table 1 = family -> product ->colour

Table 2 =  the investment cost with details at the family level

Try this code to add acolumn to the Table 1:

Investment = LOOKUPVALUE('Table 2'[Total Investment],'Table 2'[Investment ID],'Table 1'[Investment ID])

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

Appreciate your Kudos ✌️!!

[milkywaypowerbi]

Hi,

Thank you but the solution is not working. Another issue is that the investment table has 2019 while the revenue table only starts from 2020.

The investement family level is for the whole family and should not be assigned to product and colour level.

[PaulDBrown · Accepted solution]

Following modeling best practices, you need to change the structure of the model to include Dimension tables for fields common to both tables as follows:

You then use the fields from the Dimension Tables in the visuals, measures, slicers, filters... These dimension tables filter the equivalent rows in both tables.

The slicer is from the Dim Family table; the year field is from the Dim Year table. You will get:

I've attached the sample PBIX file with the changes

[milkywaypowerbi]

Hi Paul,

Thank you for your response. This is exactly what I am looking for. However, when I tried to do calculated field for profit based in the model. The numbers are not tying up.

 I tried 

Profit = CALCULATE(SUM('Revenue Data'[Revenue])-SUM(Investment[Total Investment ])-SUM('Revenue Data'[Supplies Cost])-SUM('Revenue Data'[Warranty]))

Thank you so much!

[PaulDBrown]

Just use the measures you already have:

Profit = 
[Revenue]-[Supplies cost]-[Warranty]-[Total Investment]

[milkywaypowerbi]

Hi Paul,

Thank you so much!

Just a follow up question.

If I would like to calculate NPV based on 2 different discount rate (8% for stationery, 12% for machine) from the profit, what is the best way to go about this?

For eg.

Thanks once again

[PaulDBrown]

Sorry, I'm not sure what the calculations are. Can you elaborate please?

[milkywaypowerbi]

Hi Paul,

For instance for machine I would like to discount the gross profit by 12% every year.

For 2019-> 1/(1+12%)^0* gross proft 2019

For 2020 -> 1/(1+12%)* gross profit 2020

For 2021 -> 1/(1+12%)^2 * gross profit 2021

For 2022 ->1/(1+12%)^3 * gross profit 2022

and so on.

For stationery, I would like to divide by a 8% rate instead of 12% in order to achieve the Net Present Value(NPV)

For 2019 -> 1/(1+8%)^0* gross proft 2019

For 2020 -> 1/(1+8%)* gross profit 2020

For 2021 -> 1/(1+8%)^2 * gross profit 2021

For 2022 ->1/(1+8%)^3 * gross profit 2022

How can I achieve the NPV with different rates for each product Family? Thanks!

[PaulDBrown]

OK, I've given it a shot but I'm not getting the same results as you. So I've broken it down step by step. Here goes:

First I added an order column to the Dim Year Table to use in the Power calculation:

The step-by-step measures

BTW I'm assuming the factor is (1/(1+Discount))^n 

Test Discount =
SWITCH (
    MAX ( 'Dim Family'[Family] ),
    "Machine", DIVIDE ( 1, 1 + 0.12 ),
    DIVIDE ( 1, 1 + 0.08 )
)

Test Power = MAX('Dim Year'[Order]) -1

Test Factor = POWER([Test Discount], [Test Power])

NPV = [Profit] * [Test Factor]

Final NPV = 
VAR _table = CROSSJOIN(DISTINCT('Dim Year'[L1.year]), DISTINCT('Dim Family'[Family]))
RETURN
SUMX(
    ADDCOLUMNS(_table, "FinalNPV", [NPV]), [FinalNPV])

 The [Final NPV] delivers the correct total.

So which step is off?

EDIT: you can actually fold the above into 2 measures, which we will do once the error is solved if you prefer to have 2 measures only