Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Calculate with filter measure

I have a problem trying to create a measure that would calculate a value based on certain criteria.

 

Model contains four tables:

- products (primary)

- deals (primary)

- sales

- pantheon

 

Relationships:

 

I would like to calculate average of a column in pantheon based on filters from products and then display that measure on dimDeals.

This is my current measure, which is not working:

tp_test = CALCULATE(AVERAGE(Pantheon[ergo_trasferna_cena]),FILTER(ALL(Pantheon),[SM]=1),FILTER(ALL(Products),(Products[Primary_classification]=[primary])),ALL(Products),ALL(Products))

 

So, I want to calculate average of ergo_trasferna_cena for deal id where products primary classification is same to the one of the deal id and SM is 1.

 

I hope I explained it good enough.

 

Here's a screenshot of data that I get (card) and expected data: (table)

 

 

Need Help

2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    Are [sm] and [primary] are measures or columns? Where do you show the results? then what fields are already added there?

    Try this one please:

    tp_test =
CALCULATE (
    AVERAGE ( Pantheon[ergo_trasferna_cena] ),
    FILTER ( ALL ( Pantheon ), [SM] = 1 ),
    FILTER ( ALL ( Products ), Products[Primary_classification] = [primary] )
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    It returns the same value.

     

    SM = MIN(Pantheon[Stage modifier])
    primary = CALCULATE(MAX(Products[Primary_classification]),FILTER(Products,Sales[Sales]>0))
     
    I show results in a table that contains id from dimDeals, some measures based on Pantheon columns and a measure based on Sales column