Forum Discussion

Steve_AAA's avatar
Steve_AAA
Frequent Visitor
2 years ago

Averages by Locations

I have a dataset of customer surveys. Our company has 10 locations and a customer survey can be carried out for any of these locations. At the end of the month I need to calculate the score per locat...
Need Help
DataInsights's avatar
DataInsights
Super User
2 years ago

Steve_AAA,

 

Try these measures:

 

Average Score = AVERAGE ( Scores[Score] )
Average Score All Locations = 
AVERAGEX (
    VALUES ( Locations[Location] ),
    IF ( ISBLANK ( [Average Score] ), 1, [Average Score] )
)

 

Model:

 

 

Add Locations[Location] to a matrix, and the measure Average Score All Locations:

 

 

The overall score is slightly different from yours (98.14%); please confirm.

Steve_AAA's avatar
Steve_AAA
Frequent Visitor
2 years ago

Yes that is the correct result. I'm unable to replicate it in my model though. I have 80+ locations (my 10 example was simplified). Each line on the matrix has showing the correct figure (either the average, or 100%) but the overall average at the bottom of the screen is showing only the average for those locations with survey data. Is there anything else I can try?

  • DataInsights's avatar
    DataInsights
    Super User
    2 years ago

    Steve_AAA,

     

    Can you share a link to a sanitized pbix using one of the file services like OneDrive? If not, you can attach screenshots of your data model, matrix, filters, and DAX. Be sure to indicate which fields are used in the matrix (should be Location from the Locations table, not the Scores table).