Performing a calculation involving both a measure and a scalar value give unexpected behaviour

[tbui]

Hi all,

I'm putting together what I think is a fairly simple Sales Tracking report - which tracks cummulative sales against a set target and shows what the gap is day-by-day (against both actual sales and forecasted sales).

Forecasted sales were done as a calculated column using 7-day moving averages.

And Financial Year to Date Sales (FYTDSales) was done as a measure.

Everything was working fine, as in this screenshot:

(The sales target is a number I typed and stored into a table using the "Enter Data" feature)

But as soon as I added the measure to calculate the gap into the table, each row (i.e. each "date") was then duplicated 3 times and I'm not sure why:

And as you can see, the measure definition for the gap is:

Gap = 
VAR
    Target=SUM(SalesTarget[Target])
Return
Target-[FYTDSales]

Just in case you are wondering, I've tried rephrasing that expression multiple ways. I actually started out with

Gap = SUM(SalesTarget[Target])-[FYTDSales]

When it gave that duplicated result, I tried to restructured it, using variables and such. But it doesn't help.

Even using a physical scalar value doesn't help:

Gap = 8665500-[FYTDSales]

I really need your help. Can anyone please tell me:

1. Why is the table in the report behaving in this way?

2. What is the correct way to do this?

Help would be much appreciated.

Thank you.

Tam.

[Stachu · Accepted solution]

Ad.1 most likely there is no join between your target and actuals table (maybe due to different granularity?)
as there is no join the whole target table is evaluated for each row from sales table

Ad.2 you can either create a join between tables (which may not be possible if multiple columns would be required), or you can use TREATAS to emulate that behaviour - see article below
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/