Forum Discussion

Anonymous's avatar
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]) ...
Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    6 years ago

    Anonymous -

    Does this accomplish your request?

    MeasureRed =
CALCULATE (
    [Measure],
    ALL ( FactSales ),
    Colour[Colour] = "Red"
)
  • kentyler's avatar
    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" ) )

kentyler's avatar
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's avatar
    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.