How to create measure?

[Anonymous]

I have 3 tables: Colour, Category, FactSales:

I have to create measure, to Sum amount of "Red" sales. Every row should display this value.

I tried to do it:

Measure = SUM(FactSales[Amount])

MeasureRed = CALCULATE( [Measure]; FILTER(ALL(Colour); Colour[Colour] = "Red" ) )

This is OK for this case:

But when I'm trying to use fileds from both tables it works wrong:

I expect to get 20 in each row. How can I create measure correctly and combine two ALL() functions for two tables?

[ChrisMendoza · Accepted solution]

@Anonymous -

Does this accomplish your request?

MeasureRed =
CALCULATE (
    [Measure],
    ALL ( FactSales ),
    Colour[Colour] = "Red"
)

[kentyler · Accepted solution]

What's happening is that, looking at the first line in your "both fields" example, 

Cross is filtering out 4 rows from factsales

then red filters, from within those rows, one row of red...whose amount is 6, which is what you see in your table.

So as well as getting All() from the color table, you need to get ALL() from the fact table

something like

MeasureRed = CALCULATE( [Measure];ALL(FactSales); FILTER(ALL(Colour); Colour[Colour] = "Red" ) )

[Anonymous]

---

@kentyler wrote:

MeasureRed = CALCULATE( [Measure];ALL(FactSales); FILTER(ALL(Colour); Colour[Colour] = "Red" ) )

---

Thank you. This is exact what I want.

[ChrisMendoza · Accepted solution]

@Anonymous -

Does this accomplish your request?

MeasureRed =
CALCULATE (
    [Measure],
    ALL ( FactSales ),
    Colour[Colour] = "Red"
)

[Anonymous]

---

@ChrisMendoza wrote:

@Anonymous -

Does this accomplish your request?

MeasureRed =
CALCULATE (
    [Measure],
    ALL ( FactSales ),
    Colour[Colour] = "Red"
)

---

Yes. It works perfect. Thank you!