Forum Discussion

tbrown33's avatar
tbrown33
Regular Visitor
4 years ago

Data Model measure not working

This measure was created on a data model to show the average over time to include months an entry may not be made.  This worked fine until an update was made to the model.   The new model is built in a virtualization tool (online database) that integrates the tables into a model before it is published out to Power BI.  With this format the date table in integrated into the model and does not show as a separate table.  This measure no-longer works with the new model.  It does not add a 0 to field that is blank.   We are unable to figure out why.    

Test Average Total Hours per YearMonth =

AVERAGEX(

KEEPFILTERS(VALUES(DimCalendar[FYMonth])),

CALCULATE(IF([Total Regular&Premium Hours]= BLANK(),0, [Total Regular&Premium Hours])))

 

 

This is what a visual should look like when it is properly averaging

 

What could be the cause of this?

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    tbrown33 , I doubt you should use 0, assume this is a measure - [Total Regular&Premium Hours]

     

    AVERAGEX(VALUES(DimCalendar[FYMonth]),[Total Regular&Premium Hours])

     

    or

     

    AVERAGEX(VALUES(DimCalendar[FYMonth]),[Total Regular&Premium Hours]+0)

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Try this

    Measure = coalesce([Total Regular&Premium Hours],0)

    Measure 2 = averagex(values(DimCalendar[FYMonth]),[measure])

  • tbrown33's avatar
    tbrown33
    Regular Visitor
    4 years ago

    Thanks guys.  These options have been tried, but the measures still aren't adding a 0 to the Matrix.  Power BI won't allow me to change FYMonth to a date.  Even as a decimal the 0 isn't being added.         

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support
      4 years ago

      Hi, tbrown33 

      Please try follow steps:

      Add a new seperated table for DimCalendar[FYMonth] and establish a relationship between the new table and your original table.

      New table =
DISTINCT ( DimCalendar[FYMonth] )

      Then in the matrix visual, you need to replace your original field ‘DimCalendar[FYMonth]’  with the new field ‘NewTable[FYMonth]’. In this way, your original measure should work.

       

      Best Regards,
      Community Support Team _ Eason