Forum Discussion
Aggregation of iterative date-based function
Hi,
I am struggling to figure out how to create a measure to accomplish the calculation I need. Here is my sample data:
| Unique ID | Branch | Date | Actual Revenue | Actual Cost | Actual Profit Margin % | Budget Expected Total Profit Margin % |
| 1 | A | 1/1/2025 | 100 | 80 | 20% | |
| 2 | A | 1/1/2025 | 120 | 90 | 25% | |
| 3 | B | 1/1/2025 | 80 | 50 | 38% | |
| 1 | A | 2/1/2025 | 95 | 60 | 37% | 30% |
| 2 | A | 2/1/2025 | 110 | 105 | 5% | 28% |
| 3 | B | 2/1/2025 | 90 | 50 | 44% | 40% |
The report itself will have slicers for date and branch, and the main visual will be a matrix with the latest column being the selected date, and previous columns dating back to the origin date of the data.
The measure I am trying to create should calculate, by row, the following formula:
=divide(Actual Cost, (1 - Budget Expected Total Profit Margin %),0)
where Budget Expected Total Profit Margin % is returned by row based on the selected date in the slicer.
The tricky part is, I need to aggregate that calculation by branch based on the selected branch slicer.
For example, if I have branch A and 2/1/2025 selected, I would expect to see the following result in the January column of my matrix:
Unique ID 1 = 80 / (1 - 30%) = 114.3
Unique ID 2 = 90 / (1- 28%) = 125.0
Aggregated for branch A = 114.3 + 125.0 = 239.3
I cannot achieve this result with calculated columns as they cannot be dependent on slicer values.
Any help is greatly appreciated!
I FINALLY figured it out after an entire day of research/learning. Thank you bhanu_gautam for starting me down the right path.
The aggregation issue was solved by defining several variables WITHIN an iterator function, which in this case was a sumx. Here is the final code that worked for me (please note I also had a separate calendar table 'Date' which was connected to the fact table):
FinallyWorkingMeasure =
VAR md =
DATE(year(MAXX(ALLSELECTED('Date'[Date]),'Date'[Date])),month(MAXX(ALLSELECTED('Date'[Date]),'Date'[Date])),1) -- this was just formatting the selected date to match my data which showed first day of each monthRETURNsumx('Table',VAR actualcost = 'Table'[Actual Cost]VAR latestmargin =calculate(MAX('Table'[Budget Expected Total Profit Margin %]),ALLEXCEPT('Table','Table'[Unique ID]),'Date'[Date]=md)RETURNdivide(actualcost, 1 - latestmargin, 0))
3 Replies
schaezac This measure will calculate the value for each row based on the selected date and branch slicers.
DAX
CalculatedValue =
VAR SelectedDate = MAX('Table'[Date])
VAR ActualCost = MAX('Table'[Actual Cost])
VAR BudgetMargin = CALCULATE(MAX('Table'[Budget Expected Total Profit Margin %]), 'Table'[Date] = SelectedDate)
RETURN DIVIDE(ActualCost, (1 - BudgetMargin), 0)Create a measure to aggregate the values by branch: This measure will sum up the calculated values for the selected branch.
DAX
AggregatedValueByBranch =
SUMX(
FILTER(
'Table',
'Table'[Branch] = SELECTEDVALUE('Table'[Branch]) && 'Table'[Date] = SELECTEDVALUE('Table'[Date])
),
[CalculatedValue]
)Add the AggregatedValueByBranch measure to your matrix visual to see the aggregated results based on the selected branch and date slicers.
Hi,
I actually discovered another issue.
The first measure is returning the expected value on a row basis. For example, I am getting 114.3 if I add a slicer for Unique ID 1.
However, the aggregation is not working at the branch level. If I only apply a slicer for branch A, I am not getting the expected value.
Do you know what could be causing the second measure to return incorrect values?
To be clear, the measure(s) should
1: get the latest budget margin % based on unique ID within the row, and based on date slicer then
2: perform the calculation noted above for each row then
3: aggregate the results based on branch slicer
Thank you!
I FINALLY figured it out after an entire day of research/learning. Thank you bhanu_gautam for starting me down the right path.
The aggregation issue was solved by defining several variables WITHIN an iterator function, which in this case was a sumx. Here is the final code that worked for me (please note I also had a separate calendar table 'Date' which was connected to the fact table):
FinallyWorkingMeasure =
VAR md =
DATE(year(MAXX(ALLSELECTED('Date'[Date]),'Date'[Date])),month(MAXX(ALLSELECTED('Date'[Date]),'Date'[Date])),1) -- this was just formatting the selected date to match my data which showed first day of each monthRETURNsumx('Table',VAR actualcost = 'Table'[Actual Cost]VAR latestmargin =calculate(MAX('Table'[Budget Expected Total Profit Margin %]),ALLEXCEPT('Table','Table'[Unique ID]),'Date'[Date]=md)RETURNdivide(actualcost, 1 - latestmargin, 0))
