Aggregation of iterative date-based function

[schaezac]

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!

[schaezac · Accepted solution]

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 month

RETURN 

sumx('Table',

VAR actualcost = 'Table'[Actual Cost]

VAR latestmargin = 

calculate(MAX('Table'[Budget Expected Total Profit Margin %]),ALLEXCEPT('Table','Table'[Unique ID]),'Date'[Date]=md)

RETURN

divide(actualcost, 1 - latestmargin, 0))

[bhanu_gautam]

@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.

[schaezac]

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!