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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Josué
Frequent Visitor

Conditional average

Hi,

I'm new to DAX measurements and I'm having trouble solving a problem. I need to get the average prices of the products. However, the calculation should consider only products that have data in all selected years. For example, code A has data in 2018, 2019 and 2020, code B in 2018 and 2019, in which case the average must be only for code A. If only the years 2018 and 2019 are segmented, then code B can be calculated . Sounds simple, but with my knowledge I'm not able to find a formula that does this. Here is an example of the table:

Captura de tela 2022-05-24 094542.png

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @Josué ,

 

Maybe you can try this code to do that:

 

Measure =
VAR _AllYear0fCode =
    CALCULATETABLE ( VALUES ( 'Table'[year] ), ALLSELECTED ( 'Table'[year] ) )
VAR _SelectedYear =
    CALCULATETABLE ( VALUES ( 'Table'[year] ), ALLSELECTED ( 'Table' ) )
VAR _Compare =
    COUNTROWS ( EXCEPT ( _SelectedYear, _AllYear0fCode ) )
VAR _AVG =
    AVERAGEX ( VALUES ( 'Table'[year] ), SUM ( 'Table'[sales] ) )
RETURN
    IF ( _Compare = 0, _AVG )

 

Result:

vchenwuzmsft_0-1653965204607.png

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

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

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

Hi @Josué ,

 

Maybe you can try this code to do that:

 

Measure =
VAR _AllYear0fCode =
    CALCULATETABLE ( VALUES ( 'Table'[year] ), ALLSELECTED ( 'Table'[year] ) )
VAR _SelectedYear =
    CALCULATETABLE ( VALUES ( 'Table'[year] ), ALLSELECTED ( 'Table' ) )
VAR _Compare =
    COUNTROWS ( EXCEPT ( _SelectedYear, _AllYear0fCode ) )
VAR _AVG =
    AVERAGEX ( VALUES ( 'Table'[year] ), SUM ( 'Table'[sales] ) )
RETURN
    IF ( _Compare = 0, _AVG )

 

Result:

vchenwuzmsft_0-1653965204607.png

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

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

@Josué , A measure like

 

AverageX(Summarize(Table, Table[Year], Table[Product], "_1", Sum(Table[Price])),[_1])

 

or

 

calculate(AverageX(Summarize(Table, Table[Year], Table[Product], "_1", Sum(Table[Price])),[_1]), allexcept(Table, Table[Product])

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.