Create a measure based on filter from another table

[Pillic]

Hi,

I am lost to find the right measure.

The measue should show, based on selected slicers like plant, CommercialID or Date the average of (RMQ Recipes [Wert]) where (Material list [Materialty] = CEM)

RMQ Recipes:

Material list(sample):

Relationship:

This doesnt work as expected:

CEM Average = CALCULATE(AVERAGEA('RMQ Recipes'[Wert]), Filter('Material list','Material list'[Materialty]="CEM"))

If I calculate the average by myself it should be 161,25 (see last picture) - my measue shows me 170.

Maybe someone can help me out with an additional question: Why have the values for "Wert" values behind the comma as none of them in the source have?

Thanks in advance for having a look into this.

[Pillic]

Oh no..  I think I understand the values now and it seems that all is correct calculated as Amit mentioned. It was my wrong understanding of the calculation, so I added the SUM and the COUNT to follow the "irritation" and that explained it to me...I calculated the wrong column while power bi does calculating the right values.

Thanks for helping me finding the issue 😉 

[Icey]

Hi @Pillic ,

Glad to hear that you have found the issue. You may help accept the replies making sense as solution above. Your contribution is highly appreciated.

Best Regards,

Icey

[Greg_Deckler]

@Pillic - Seems like AVERAGEX(FILTER(RELATEDTABLE(...),...),...)

[Pillic]

Hi Greg,

this fails for me:

CEM Average = AVERAGEX(Filter(RELATEDTABLE('Material list'),'Material list'[Materialty] = "CEM"),'RMQ Recipes'[Wert])

What am I doing wrong?

[Icey]

Hi @Pillic ,

How about create another measure like so:

Measure =
IF (
    [CEM Average] <> BLANK (),
    AVERAGEX (
        ALLSELECTED ( 'RMQ Recipes'[Material] ),
        CALCULATE ( [CEM Average] )
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Pillic , there is nothing wrong with formula. you can use average and check.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.