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,

 

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

Just use the measures you already have:

 

 

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

 

 

 

 

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

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

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

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