Don't Summarise returns unexpected values

[Anonymous]

Hi Community,

I have re-created a simple Model based on the real dataset i am working with.

Taget Fact: Target %

Sales Fact: Sales Value and Discount Value

Dim Week: Week

Dim Store: Store

Model

Sales Fact

Target Fact

The target for each Store is the Target % (Target Fact) that we need to stay under for each Store on each Week.

However, when i have multiple Weeks selected, i dont want them to be summed up, as that will not make sense. 

For this reason i used the option 'Don't Summarize' as the below shows:

Don't Summarise selected for 'Target'

Which works great as it does not show the Down Total of 6.8% (3.4+3.4).

However, when i go ahead and create a Measure for the Sales and add it into the Table visual i get the below:

Displays incorrect 'Target'

The result repeats each record for as many 'Targets' values that exist in the 'Target Fact', even though a relationship between the Store 'King' and Week '1901' or '1902' does not relate back to 5.5%

So, the question i have is: Why do all the Target attributes appear in the Table Visualisation, when i first select 'Don't Summarise' and then add a SUM sales Measure?

Hope this makes sense as wrote in rush!

Note: I have found a solution to my issue which was using an ABG Function, however i just want to undertand how the Don't Summarise options works.

Thanks,

Laz

[v-lili6-msft]

hi, @Anonymous

For your data model, Sales field could not achieve Target field, it likes a many to many relationship.

and for all the data from Sales and Target table, the value must be summarise, otherwise, it will like two tables without relationship 

and drag their value into one visual, the result will be cross jion for each other.

Best Regards,

Lin

[Anonymous]

Hi Lin,

Thanks for your repsonce.

I would like to pick up on something you said:

"all the data from Sales and Target table, the value must be summarise, otherwise, it will like two tables without relationship"

As you saw from the model and relationhsips (pictures posted previously) the Fact Sales and Fact Target both have a 1:M relationship with the Dimesnions. Can you please explain your comment a bit more in depth?

The purpose of this from my side, is not to make this model 'work', but instead undertand the behaviour of the 'Dont Summarise' option when using a SUM measure with it.

Thanks,

Laz