Calculate a measure by individual date period, instead of calculate over entire table

[grubpot]

Hello,

A follow up question for an item I posted that was 90% solved!

I have a measure that I need to summarise by date. Instead of calculating over the entire table.

I can't see how to insert sample file, so below is some example data:

Current data structure has product, customer, date, forecast and ordered quantity.

Product	Customer	Date	Forecast	Ordered
A	Joe Bloggs	01-Jan	100	85
A	Bill Gates	01-Jan	75	70
A	Mike Smith	01-Jan	60	80
A	Eddie Jordan	01-Jan	50	90
A	Brian Bones	01-Jan	55	50
A	Jim Bob	01-Jan	80	70
A	Ricky Smart	01-Jan	90	80
B	Joe Bloggs	01-Jan	60	85
B	Bill Gates	01-Jan	50	70
B	Mike Smith	01-Jan	55	80
B	Eddie Jordan	01-Jan	80	90
B	Brian Bones	01-Jan	90	50
B	Jim Bob	01-Jan	60	80
B	Ricky Smart	01-Jan	50	70
C	Joe Bloggs	01-Jan	55	80
C	Bill Gates	01-Jan	80	85
C	Mike Smith	01-Jan	90	70
C	Eddie Jordan	01-Jan	60	80
C	Brian Bones	01-Jan	50	90
C	Jim Bob	01-Jan	55	50
C	Ricky Smart	01-Jan	80	90
A	Joe Bloggs	02-Jan	55	50
A	Bill Gates	02-Jan	80	70
A	Mike Smith	02-Jan	90	80
A	Eddie Jordan	02-Jan	60	85
A	Brian Bones	02-Jan	50	70
A	Jim Bob	02-Jan	55	80
A	Ricky Smart	02-Jan	80	90
B	Joe Bloggs	02-Jan	90	50
B	Bill Gates	02-Jan	60	80
B	Mike Smith	02-Jan	50	70
B	Eddie Jordan	02-Jan	55	80
B	Brian Bones	02-Jan	80	85
B	Jim Bob	02-Jan	90	70
B	Ricky Smart	02-Jan	100	80
C	Joe Bloggs	02-Jan	75	90
C	Bill Gates	02-Jan	60	50
C	Mike Smith	02-Jan	50	90
C	Eddie Jordan	02-Jan	55	50
C	Brian Bones	02-Jan	80	70
C	Jim Bob	02-Jan	90	80
C	Ricky Smart	02-Jan	60	85
A	Joe Bloggs	03-Jan	50	70
A	Bill Gates	03-Jan	55	80
A	Mike Smith	03-Jan	80	90
A	Eddie Jordan	03-Jan	60	50
A	Brian Bones	03-Jan	50	80
A	Jim Bob	03-Jan	55	70
A	Ricky Smart	03-Jan	80	80
B	Joe Bloggs	03-Jan	90	85
B	Bill Gates	03-Jan	60	70
B	Mike Smith	03-Jan	50	80
B	Eddie Jordan	03-Jan	55	90
B	Brian Bones	03-Jan	80	50
B	Jim Bob	03-Jan	60	90
B	Ricky Smart	03-Jan	50	50
C	Joe Bloggs	03-Jan	55	70
C	Bill Gates	03-Jan	60	80
C	Mike Smith	03-Jan	50	85
C	Eddie Jordan	03-Jan	55	70
C	Brian Bones	03-Jan	80	80
C	Jim Bob	03-Jan	60	90
C	Ricky Smart	03-Jan	50	50

Then my measure works out the ordered vs forecast at product level.

a  +/-15%  difference between forecast and ordered is acceptable.

Date	Product	Total Forecast	Total Ordered	Tolerance
01-Jan	A	510	525	3%
01-Jan	B	445	525	18%
01-Jan	C	470	545	16%
02-Jan	A	470	525	12%
02-Jan	B	525	515	-2%
02-Jan	C	470	515	10%
03-Jan	A	430	520	21%
03-Jan	B	445	515	16%
03-Jan	C	410	525	28%

then finally, this is the resuly I need. The number of products that hit the target +/- 15% by date.

I have a formula for the measure, but it only looks at the entire block of data, whereas I need to summarise by date.

	Total products ordered	Number of products in acceptable  tolerance	Tolerance Rate
01-Jan	3	1	33%
02-Jan	3	3	100%
03-Jan	3	1	33%
Grand Total	9	5	56%

Would anyone have any idea how to achieve this?

Many thanks

[grubpot]

If I put my measusre into a table it gives the correct result for the individual dates.

However the total does not work - it looks at all the data together instread of the total of each individual result.

[grubpot · Accepted solution]

Hi,

I found a solution... the solution was to use summarize!

Accuracy count =

VAR summarizedTable =

SUMMARIZE('Weekly Forecast Accuracy set datesnew','SAP dates'[Week / Yr],"Count acc", [Count in tolerance])

RETURN

SUMX(summarizedTable,[Count in tolerance])