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 location. This is fine for individual locations as I can average the score across the survey submissions and show it by location. But if a location hasn't had any customer surveys completed this month, that location must show as 100%. I have no record to in the dataset to show this and I don't want to create them. I do have a locations table in my dataset. 

 

Example: 

 

Scores Table:

Location 1, Survey 1: 100%

Location 1, Survey 2: 95%

Location 1, Survey 3: 80%

Location 1, Survey 4: 93.5%

Location 1, Survey 5: 100%

Location 2, Survey 1: 100%

Location 3, Survey 1: 50%

Location 3, Survey 2: 88%

 

Locations Table:

Location 1

Location 2

Location 3

Location 4

Location 5

Location 5 etc to location 10...

 

Table Showing Scores by Location:

Location 1: 93.7%

Location 2: 100%

Location 3: 69%

Location 4: 100%

Location 5: 100%

Location 6: 100% etc to location 10...

Overall Score: 98.14%

(93.7%, 100, 69% and all the other locations default to 100%)

Need Help

4 Replies

  • 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).