Forum Discussion

Josué's avatar
Josué
Frequent Visitor
4 years ago
Solved

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 a...
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
4 years ago

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.