Sum of rows that satisfy a calculated measure in Power BI

Question

All.

I have a set of data that includes the following:

• Store Name
• Survey ID
• Number of surveys per store
• Score

Each survey has a score, and then the overall score for that store is calculate as an average.

The score is a calculated measure based off 3 other calculated measures. These are created as below:

Overall Score 1 = CALCULATE(CALCULATE(COUNT('Table'[Score 1]), 'All Detail'[Score 1] = 5) / (COUNT('Table'[Score 1]))) * 100

Overall Score 2 = CALCULATE(CALCULATE(COUNT('Table'[Score 2]), 'All Detail'[Score 2] = 5) / (COUNT('Table'[Score 2]))) * 100

Overall Score 3 = CALCULATE(CALCULATE(COUNT('Table'[Score 3]), 'All Detail'[Score 3] = 5) / (COUNT('Table'[Score 3]))) * 100

Score = ([Overall Score 1] + [Overall Score 2] + [Overall Score 3])/3

I am wanting to count how many stores have beat a target score.

Currently I have a calculated measure which indicates that the store has beat the target:

Achieved = IF([Score] >= 60, 1, 0)

This works well on a cube at Store level, telling me that the average score for the store is over 60 or not, as below:

However, I am struggling to get the total of stores that have achieved target. In the example above the total would be 3 stores.

I have tried to create a filter measure as below:

Total Achieved = COUNTROWS(FILTER('Table', [Achieved] = 1))

However, this brings back the number of surveys per store that have beat the target. Examples below:

As you can see the Total Achieved calculated measure works well at a survey level but this is not the desired output.

Example of what I am looking for as the output below:

Any advice would be appreciated. I have tried to use calculated columns but with no luck.

Answer 1

Hi this is the expected behavior of table visual first step is to create a summarize table as below. Here I have only group by from ‘Table'[Store] use your own grouping conditions. s

sumtable = 
    ADDCOLUMNS(
    
         SUMMARIZE(
    
              ‘Table’,
    
              ‘Table'[Store],
            
         ),
    
         ”Surveys”,SUM(Table[Surveys]),
         "Score" ,[Score]
    
    )

After that for the above summarize table create the measure as below

Achieved = IF(sumtable[Score] >= 60, 1, 0)

This will ensure your results.