Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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] ) )
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] ) )
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |