Calculation of the sum at dimension level leads to the appearance of incorrect records in the table

[Alyona_BI]

Hi everyone! 

I've been struggling with this problem for a few days, and I really hope for your help.

I have the following data model: 

I need to show the sum of [ST] at the [Line] level in the following tables (with current selections line LB6 has the value 683,243 and line LC7 has the value 275,367, and I need to see these values in each record for corresponding [Line]): 

The key difficulty here is that this sum at [Line] level:

- should be affected by selection in slicers [Line], [Object Type] and [Machine Description] (so if the user select something in these slicers, then the total sum could change); 

- should NOT be affected by selection in slicers [Machine], [Equipment Type] and [FLoc ID] (the selection in these slicers don't change the sum at [Line] level).  

I used the following formula: 

Sum at line level =

 CALCULATE(

     SUM('Table 3'[ST]),

     ALLSELECTED('Table 2'[Machine Description], 'Table 2'[Object Type]),

     ALL('Table 1'[Equipment Type], 'Table 1'[Machine], 'Table 1'[FLoc ID])

 )

It gives me desired result but it leads to a lot of not correct records in the tables. For example, in second table I have 8 records: 

But in reality here I should have just 4 records for which the relations in the data model are exists. 

Here you could find the pbi app with data: https://drive.google.com/file/d/1mQvdpE9apnqv6NbsdyIaJdLd74KuYd7O/view?usp=sharing

Please help me to remove the incorrect records from the tables. 

Many thanks in advance! 

Alyona

[lbendlin · Accepted solution]

In the green box you are mixing filters from the fact table (Table 1) and the dimension table (Table 2).  That's a red flag. Ideally filters should only be applied at the dimension levels.

You can "fix" your visual by excluding rows where the "simple sum" measure is blank.