Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

How to create measure?

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?

Anonymous -

Does this accomplish your request?

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

kentyler
6 years ago

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" ) )

4 Replies

ChrisMendoza
Resident Rockstar
6 years ago
Anonymous -
Does this accomplish your request?
MeasureRed = CALCULATE ( [Measure], ALL ( FactSales ), Colour[Colour] = "Red" )
- Anonymous
  Not applicable
  6 years ago
  ChrisMendoza wrote:
  Anonymous -
  Does this accomplish your request?
  MeasureRed = CALCULATE ( [Measure], ALL ( FactSales ), Colour[Colour] = "Red" )
  Yes. It works perfect. Thank you!
kentyler
Solution Sage
6 years ago

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
  Not applicable
  6 years ago
  kentyler wrote:
  MeasureRed = CALCULATE( [Measure];ALL(FactSales); FILTER(ALL(Colour); Colour[Colour] = "Red" ) )
  
  Thank you. This is exact what I want.

Related content