Create a visual with data from various tables

[JessicaVanti]

Hi everyone,

I am not too good at explaining things, and I hope the image below can help me convey my issue:

I have a table indicating how much fruit was sold and when. I have another table that tells me how much I should have sold in 2023.

I want to create a table that compares the data and tells me how much fruit is still needed to be sold to reach the target.

I'm afraid the solution might be very simple, but I've been trying for days and I still can't get the result I want.

[JessicaVanti]

You were super clear and you even gave me the exercise. Amazing!

Pete, thank you so much. 

[BiNavPete · Accepted solution]

Hi @JessicaVanti 

See attached PBIX.
2 fact tables - data and target.
Data is as in your screenshot. Target is unpivoted on Target 2023 column and then that column reoworked to provide a year in each row. Doing it this way will solve future or past targets if you had adjacent columns Target 2022, Target 2024 in the original table.

Create following dimension tables from data:
Years - reference Data table, delete all cols other than year and deduplicate
Fruits - reference Target table and delete all cols other than fruit.

Bring all this lot to Power BI with close and apply.
Create single directional, one to many relationships from fruit table to Data and Target. Ditto for years.

Now we have a model that DAX will like.
Create Measures:
[Qty Sold] = Sum(Data[Qnty Sold])
[Target] = sum(Target[Value])
Delta = [Qty Sold] - [Target]

Then bring the columns into a table visual with a year slicer.

Boom!

Link here to PBIX.

Hope you get on OK

Pete