Forum Discussion

milkywaypowerbi's avatar
milkywaypowerbi
Icon for Helper II rankHelper II
5 years ago
Solved

Connecting Two Tables to form one matrix table

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 tri...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    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's avatar
milkywaypowerbi
Icon for Helper II rankHelper II
5 years ago

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's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
5 years ago

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