Forum Discussion

EDDYYC's avatar
EDDYYC
Frequent Visitor
5 years ago
Solved

Calculate average only if value exist

Hello community,

 

I have the following scenario, currently i have the following table with 27 values in it,

 

 

the relationship is table1 ID with the table2 ID which is the _Last expression, table 1 contains all of the 27 values but not all of them exist in table2. There is a slicer to select the month and this relationship is by year-month value between table dates and table 2.

 

 

As you can see when i select April it calculates the values for the month of April if the ID is in table 2 of course.

 

As you can see in the _SELEC column there are blank values which means there is no data for that specific ID in the selected month, how can i assign a value of 0 to those blank values if they do not exist in the table 2?

 

i have this current expression but not working:

_SELEC =

VAR _TT = CALCULATE( LASTNONBLANK( Dates[CalendarYearMonth], 1), FILTER( Dates, Dates[MonthYear] = SELECTEDVALUE( Dates[MonthYear])))
VAR _ER = CALCULATE( AVERAGE( 'Sent Pigs'[Score_1]), FILTER( 'Sent Pigs', 'Sent Pigs'[YearMonth] = _TT))
RETURN IF([_last], _ER, 0)
 
Sent pigs is table 2.
Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    There's a DAX function for that - COALESCE()

     

    However - you'll want to make sure that that approach makes sense from a business perspective too.

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    There's a DAX function for that - COALESCE()

     

    However - you'll want to make sure that that approach makes sense from a business perspective too.