Forum Discussion

Alina12's avatar
Alina12
Icon for Helper I rankHelper I
2 years ago
Solved

Calculated column across different tables and with different criteria

Hi guys!

 

I am looking for a solution for a rather complex calculation and I have the feeling that I can't see the wood for the trees.

I have two tables in my Power BI (example modeled in Excel). Table 1 shows me products, the associated price, when it was started to be processed (started in sprint) and what the product was delivered (delivered sprint) and to whom (team).

 

In table 2 I have teams, which company is involved in the teams, a sprint and a % value (the % value results from a different calculation, which I don't think is relevant here). So much for my database.


I would now like to add a calculated column to Table 2 that calculates the delivered value per company. To do this, the price of each product (in the sprint in question) must be multiplied by the individual percentage of the company, which is dependent on started in sprint. I have inserted an example matrix here (which should also represent the final result in my report) and what the calculated final column in table 2 should look like.


Does anyone know what the DAX formula for my calculation might look like? I just can't figure it out. 

Thanks in advance!

 

 

Need Help
  • mickey64's avatar
    mickey64
    2 years ago

    Step 1: I add a column to Table1 and Table2 in Power Query Editor.

     

    Step 2: I make 3 Tables and add some relationships.

        Key Column Table = SUMMARIZE('Table2','Table2'[Key Column])

        Sprint Table = SUMMARIZE('Table2','Table2'[Sprint])

        Team Table = SUMMARIZE('Table1','Table1'[Delivered by])

     

    Step 3: I make a mesure and a matrix.

        M_C1 Value = SUM(Table1[Price])*SUM(Table2[Rolling Total (%)])

     

     

     

     

     

2 Replies

  • mickey64's avatar
    mickey64
    Icon for Super User rankSuper User
    2 years ago

    Step 1: I add a column to Table1 and Table2 in Power Query Editor.

     

    Step 2: I make 3 Tables and add some relationships.

        Key Column Table = SUMMARIZE('Table2','Table2'[Key Column])

        Sprint Table = SUMMARIZE('Table2','Table2'[Sprint])

        Team Table = SUMMARIZE('Table1','Table1'[Delivered by])

     

    Step 3: I make a mesure and a matrix.

        M_C1 Value = SUM(Table1[Price])*SUM(Table2[Rolling Total (%)])

     

     

     

     

     

  • Alina12's avatar
    Alina12
    Icon for Helper I rankHelper I
    2 years ago

    Hi mickey64 

    First, thanks for your help!
    That sounded very promising and I implemented it exactly as you said. However, it doesn't work and in my calculated column in table 2 it only gives me very strange totals that I can't explain (the same sum in every row). Do you have any ideas or an alternative approach?

    I was trying this for my calculated column in table 2, but it shows me just blank values:

    DeliveredValue =
    VAR StartedSprint = MAX('Table1'[started in sprint])
    VAR DeliveredSprint = MAX('Table1'[Delivered sprint])
    VAR DeliveredBy = MAX('Table1'[TeamID])
    VAR CurrentCompany = MAX('Dim_Companies'[CompanyID])
    VAR PercentValue = CALCULATE(
            MAX('Table2'[Rolling Total (%)]),
            'Table2'[Sprintnumber] = StartedSprint,
            'Table2'[TeamID] = DeliveredBy,
            'Table2'[CompanyID] = CurrentCompany
        )
    VAR StoryPrice = CALCULATE(
            SUMX(Table1,'Table1'[Price]),
            FILTER( 'Table2',
                'Table2'[Sprintnumber] = DeliveredSprint &&
                'Table2'[TeamID] = DeliveredBy  
                )
        )

    RETURN

     StoryPrice * PercentValue