Subtotal by one report column in another column

[Anonymous]

Hi

I'm trying to produce a report that has a subtotal column for a particular field filtered by <= date that is output on the pivot table (excel) and I'm struggling to get it to work. It seems like it should be really easy, but perhaps not!

Example Data

Date-------- Reference 1------------------Reference 2--------------Amount

01/07/19----A100_________________________U100___________________200.00

01/08/19----A100_________________________U100___________________-100.00

01/07/19----A100_________________________U200___________________-100.00

01/08/19----A101_________________________U101___________________150.00

01/07/19----A101_________________________U102___________________200.00

01/07/19----A101_________________________U102___________________-300.00

01/08/19----A101_________________________U102___________________150.00

01/09/19----A101_________________________U102___________________150.00

I have a date table in my model. I want to have a measure of Total by Reference1 and a measure of Total by Reference2 like:

Ref1Total:=CALCULATE(SUM(Amount),FILTER(ALL(dDate),dDate[Date]<=MAX(dDate[Date])&&data[Reference1]=VALUES(data[Reference1])))

and

Ref2Total:=CALCULATE(SUM(Amount),FILTER(ALL(dDate),dDate[Date]<=MAX(dDate[Date])&&data[Reference2]=VALUES(data[Reference2])))

To produce an output of the following when filtering on date 01/08/19: 

Reference 1------------------Reference 2--------------Ref1Total------Ref2Total

A100_________________________U100___________________0.00____________100.00

A100_________________________U200___________________0.00____________-100.00

A101_________________________U101___________________200.00__________150.00

A101_________________________U102___________________200.00__________50.00

Individually, my measures work fine, but when I add Reference 1 and 2 together the reference 1 total appears to be affected by the Ref2 column being added. In the above example, the Ref1Total for the first line reads 100 and the second reads -100 when I output it. Any suggestions on how I add a subtotal by Reference1 measure for each line?

Thanks,

Rico 

[Anonymous · Accepted solution]

Ref1Total :=
var __lastVisibleDate = MAX( dDate[Date] )
return
	CALCULATE(
		SUM( data[Amount] ),
		dDate[Date] <= __lastVisibleDate,
		ALLEXCEPT( data, data[Reference1] )
	)
	
Ref2Total :=
var __lastVisibleDate = MAX( dDate[Date] )
return
	CALCULATE(
		SUM( data[Amount] ),
		dDate[Date] <= __lastVisibleDate,
		ALLEXCEPT( data, data[Reference2] )
	)

[Anonymous]

I tried an ALLEXCEPT() the other day and couldn't get it to work as I was then filtering by date, rather than adding the date filter into the calculate function as you have done. That works perfectly, thanks!