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.
I have a simple sales table below: 'Sales'
This thrown into a simple Matrix looks like this:
My objective is to move the TOTALs to the front of the matrix (left) and then to create a measure that results in a running total week over week of the Goal amounts. To do this I created a new table ( 'Week Order' ) to sum all data across all date points. See picture below:
I then related the two tables on 'Sales'[Week] and 'Week Order'[Week Date]
This allows me to use 'Week Order'[Week Of] as the column object in my matrix.
Now I need to create a measure to calculate the running total of the Goal amounts.
Goal Running Total = CALCULATE(
sum(Sales[Goal]),
FILTER(
all('Week Order'[Week Of]),
'Week Order'[Week Of] <= MAX('Week Order'[Week Of])
)
)
When I throw this measure into the matrix everything works fine before sorting [Week Of]. You can see below that the running total for goals adds each week. 4/10/23 = 250, 4/17/23 = 100 + 250, 4/3/2023 = 125+ 100 + 250
Now, when I sort [Week Of] by [Order] the measure no longer compiles the Goal total each week.
This is the problem I need help solving.
Try this version
Goal Running Total =
var mx = MAX('Week Order'[Week Of])
return CALCULATE( sum(Sales[Goal]),'Week Order'[Week Of] <= mx)
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 |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |