How to sum a linked table independent of the table it is linked to

[Saarek]

I have two data tables, one containing sales metrics and another based on sales targets. The two tables have been linked by a special "bridge" column that I created that is based on Year, Month, Product and Branch.

The issue I have run into is that if a combination of the above is not available yet in the sales data then the target for that combination is also missing from my KPI indicators.

Example, for January we may not yet have had any sales for Birmingham with the product of Forklifts. There is a target for combination of '2021, January, Forklifts, Birmingham' but as there is no sale information for that combination the target also does not reflect on the KPI.

How do I force Power BI to completely sum the Target against Sales even if the bridge combination is not yet present in the Sales dataset?

I should have a total target for January of £56,000,000 but I currently only see £24,000,000.

[amitchandak]

@Saarek , How you create bridge table, It should have data from both tables. refer: https://www.youtube.com/watch?v=Bkf35Roman8

for Date create a date table - To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Saarek]

Hi @amitchandak The bridge column is created within an R Process, Power BI runs the R script and the data is ingested that way.

[AllisonKennedy]

@Saarek  How have you created the visual? If the value is in the target table, then you should see it in the report, can you share screenshots or more info on what you've done that's making it missing?

[Saarek]

Hi There @AllisonKennedy 

The bridge column is the same format on both datasets, example here pasted below:

Think of AP05 as the equivalent of May.

The join is based on a many to one relationship (targets being single line per bridge and sales having multiple) and set to apply both ways.

For the KPI I have the Target value set as Sum of NB_£ (this is from the target dataset) and the value set to NB_Actual from the sales dataset.

If I was to sum the target sheet in excel, based on January only (AP01) for 2021 the sum comes to £5.76M, but on my KPI it shows as £4.34M. I have realised that this is because some of the bridge combinations do not yet reflect on the sales dataset side, as such it has also dropped those target values.

[AllisonKennedy]

@Saarek  Are you using the built in KPI visual? It looks like you may be using the Gauge visual actually? Can you try viewing in table visual briefly for testing purposes, and see what's happening. I find this to be more intuitive for teaching/troubleshooting.

We also need to know what other slicers or context is happening in the report. Have you filtered the page for date/product/etc? Which table are those slicers coming from?

What is your Date table and how is it connected?

I think a better model in this scenario would be to have 2 fact tables and connect them to Dim tables using 1 to many. Use only the Dim tables in your filters/slicers. For example, you may need to connect date table to both target and sales, create a DimProduct and connect to target and sales. Create a DimBranch and connect to both fact tables.  Remove the bridge table and both cross filter direction. Use only the Dim tables as slicers. This should help.