topic Re: Conditional average in DAX Commands and Tips

Conditional average

Josué — Tue, 24 May 2022 13:19:56 GMT

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:

Re: Conditional average

amitchandak — Wed, 25 May 2022 07:09:40 GMT

Josué , A measure like

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

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

Re: Conditional average

v-chenwuz-msft — Tue, 31 May 2022 02:47:39 GMT

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:

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.