topic Re: Visualisation not showing correct results from multiple tables in DAX Commands and Tips

Visualisation not showing correct results from multiple tables

Anonymous — Fri, 02 Aug 2024 10:45:31 GMT

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

Re: Visualisation not showing correct results from multiple tables

ValtteriN — Fri, 02 Aug 2024 11:00:36 GMT

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/

Re: Visualisation not showing correct results from multiple tables

Anonymous — Fri, 02 Aug 2024 11:18:59 GMT

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

Re: Visualisation not showing correct results from multiple tables

ValtteriN — Fri, 02 Aug 2024 11:44:28 GMT

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.