Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX for Summarizing at an aggregate level

I have two tables. One with the total sale amount for the month by car and another with the details showing car, model, and sales person. I have merged the to tables together and need to find a way to write dax that gives me the sum of the amount sold without over counting. **NOTE in my example I do not have the ability to split these tables out into two tables and relate them so I need a DAX that can work off the belnded fact table (Table 3).

 

I need a DAX measure that sums the amount for cars sold. In table 1 below the total amount that was sold was $2400. (1000 + 200 + 400 + 800). You can see on the green merged table 2 below those amount values are duplicated and I need to be able to sum the column, but sum the unique values by car.

 

Sales Amount for the month by Car (Table 1)

 

Sales Detail (Table 2)

 

Table 3: Blended Fact Table (Main Fact Table I need DAX built off of)

 

Here is the formula I have showing the "INCORRECT" value....

Sales Amount:=SUM(Table3[Amount])

 

7,200 is not the right amount, it should be 2,400 for the month.

 

I need a dax formula that shows me this....

 

How do you write dax off Table 3 that gives you the table above showing 2,400, if the amount values from the first table are duplicated when merged with the second table?

3 Replies