Combining 2 tables into one for Volume calculations

[dreyz64]

Hello everyone,

We have just developped an algorithm to predict our sales in our various shops and for our various products.

Now we are trying to create a PBI report that would allow us to compare these values against the sales that were forecasted manually by our sales teams and with the real sales values (for the past).

For this, I have 3 tables:

- Manual Forecast Table

- Algorithm Table

- Real Sales Table

Now the tricky thing is that some combinations (example: Shop 1 & Product 1) can be present in one table but not the others.

In SQL i know that I could do a union of all combinations and then query based on this.

However this is a lot of data and it takes forever to load.

I wanted to know if within PBI it was possible to merge them into one table with all lines.

Something like this:

LIke this i would not have to create a table that contains all the combinations of all 3 tables.

Thanks a lot!

[parry2k · Accepted solution]

@dreyz64 In my opinion you don't need to combine these tables , assume these are fact tables and you can dimension to work with this and can visualize the data from multiple fact table.

in this case you will have 3 dimension tables

- Store (unique store)

- Product (unique products)

- Date  (date dimension)

set relationship with these both tables from all these 3 dimension, dimensions will be on one side of the relationship.

In table visual, drop store, product and date column from dimension and Volume from respective table (forecast and algorithm) and everything would work.

[dreyz64]

Hi @parry2k !

Thank you for your answer.

I had tried an approach (maybe a bit similar) which was to create a big table with all my distinct combinations of:

- Store, Product & Dates

And then created 3 tables for my Forecast, Algo and Real sales.

And then i tried to create a relation ship between the main table and the 3 ones.

However it is not giving me good results.

I am not sure I understood your method.

What I did now is I created this:

- 1 table with all Geo hierarchies (unique)

- 1 table with all product hierarchies (unique)

- 1 table date (unique)

 then I have my tables with my volumes (e.g. the whole geo hierarchies, product hierarchies, dates & volumes).

I have created my relationships but the question now is how do I link my geo hierarchies with my product hierarchies?

I am missing a step...

[parry2k]

@dreyz64 Not sure what define not getting good results.

share, sample pbix and what is not working.

[parry2k]

@dreyz64 can you share pbix file with sample data?

[dreyz64]

@parry2k 

Unfortunately I will need to anonymise the workbook as I cannot share it as is.

Let me try to explain what I did. I created 3 new tables: 

- Date Table

- Shop Table

- Product Table

They are linked to my data tables with a relationship 1 to many, single.

The thing is now, if i want to do a matric with Shops & Products in rows, Date in columns and volumes in values, it does not work because I have no link between the Product table and the Shop Table.

Should I make an extra table that contains all the combinations of Shop + Products?

[parry2k]

@dreyz64  you don't need relationship between product and shop table, as soon as you drop the volume from these tables in the values , everythig will work Did you tried that?

[dreyz64]

@parry2k I managed to solve my issue by creating a single main table of metadata and linking it to my tables with my values!
Thank you for your help 🙂