Calculate with filter measure

[aljazg]

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)

[aljazg]

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

[Fowmy]

@aljazg 

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