cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tbui Frequent Visitor
Frequent Visitor

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

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:

 

20190522 - Working 01.JPG

 

(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:

 

20190522 - Not Working 01.JPG

 

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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/

1 REPLY 1
Super User
Super User

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

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/