Finding the Best Result Based on Multiple Criteria

[hassens]

Hi,

I have a data set similar to the one below. I want to highlight/identify the best result (where answer = 'yes') for each question for each store within a given period. Only one best result should be highlighted given the criteria above so something like the first or last best result would be fine. I have added the intended result in the last column below.

Visit ID	Store ID	Question	Answer	Period	Date	BestRead?
1	A	Question 1	Yes	1	01/01/2017	1
1	A	Question 2	Yes	1	01/01/2017	1
2	A	Question 1	Yes	1	02/01/2017	0
2	A	Question 2	Yes	1	02/01/2017	0
3	A	Question 1	Yes	2	03/05/2017	1
3	A	Question 2	Yes	2	03/05/2017	1
1	B	Question 1	Yes	1	05/01/2017	1
1	B	Question 2	No	1	05/01/2017	0
2	B	Question 1	No	1	10/05/2017	0
2	B	Question 2	Yes	1	10/05/2017	1

What I have been trying is:

1. Adding a column to translate all 'Yes' answers to '1', called YesRead
2. Trying the following: BestRead = IF(Table[YesRead]=CALCULATE(MAX(Table[YesRead]),ALLEXCEPT(Table,Table[StoreID],Table[Period],Table[Question])),1,0)  which returns a 'A single value for column 'YesRead' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.' Also I realise this does not yet incorporate anything to highlight only one best within the criteria.

Any help would be appreciated!

Thanks 

[Anonymous]

Hi @hassens,

>>I want to highlight/identify the best result (where answer = 'yes') for each question for each store within a given period.

You can try to use below formula to summary records by "question" and "period".

Table = SUMMARIZE(FILTER(ALL(Sheet1),[Answer]="Yes"),[Store ID],[Question],[Period],"Total Read",SUM(Sheet1[BestRead?]))

I haven't found a way to highlight records which in the table.

Regards,

Xiaoxin Sheng