Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Pillic
Helper II
Helper II

Create a measure based on filter from another table

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:

Pillic_2-1599230708686.png

 

Material list(sample):

Pillic_1-1599230628778.png

 

Relationship:

Pillic_3-1599230831802.png

 

image.png

 

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

6 REPLIES 6
Pillic
Helper II
Helper II

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.

 

Pillic_0-1599476764382.png

 

Thanks for helping me finding the issue 😉 

Icey
Community Support
Community Support

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
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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
Community Support
Community Support

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
Super User
Super User

@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.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.