Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Visualisation not showing correct results from multiple tables

Hi, so im reletively new to powerbi but seem to be making good ground.

 

However I have an issue where im trying to have a visual bring back results from two different tables

 

Ive screenshot the relationships below. Basically Im looking to have a table visualisation which shows the category and the the contract value and the amount spent against the contract. The Contracts are in the register and the spend in the spend table. To link them I had to create a intermidary table which had the supplier account code which is in both tables multiple times (I could link the tables directly because of this as multiple contracts have the same supplier account code and multiple invoices are listed in the spend table with the account code.

 

In the output example below im taking the category level 1 field from the contracts table, the total contract value from the contracts table and the amount excluding vat from the spend table. However the figure reported from the spend table is the total for all invoices.

 

How do i correct this so i can see the contract value and spend by category?

 

Thanks

 

 

  • ValtteriN's avatar
    ValtteriN
    2 years ago

    In the example I provided:

    Measure 32 =
    var _supplier = MAX('Table (42)'[Supplier]) RETURN
    CALCULATE(SUM('Table (41)'[Value]),'Table (41)'[Supplier]=_supplier)

    Here we take the supplier information from one table and use it to calculate the corresponding value from the other.

3 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi,

    Use calculate and modify the filter context:

    Example tables:

    Simple sum won't work:

     


    However this will work:

    Measure 32 =
    var _supplier = MAX('Table (42)'[Supplier]) RETURN
    CALCULATE(SUM('Table (41)'[Value]),'Table (41)'[Supplier]=_supplier)

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Apologies if a simple question but how to you modify the filter context?

      • ValtteriN's avatar
        ValtteriN
        Icon for Community Champion rankCommunity Champion
        2 years ago

        In the example I provided:

        Measure 32 =
        var _supplier = MAX('Table (42)'[Supplier]) RETURN
        CALCULATE(SUM('Table (41)'[Value]),'Table (41)'[Supplier]=_supplier)

        Here we take the supplier information from one table and use it to calculate the corresponding value from the other.