Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
schaezac
Frequent Visitor

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 IDBranchDateActual RevenueActual CostActual Profit Margin %Budget Expected Total Profit Margin %
1A1/1/20251008020% 
2A1/1/20251209025% 
3B1/1/2025805038% 
1A2/1/2025956037%30%
2A2/1/20251101055%28%
3B2/1/2025905044%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!

 

1 ACCEPTED SOLUTION
schaezac
Frequent Visitor

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))
 

View solution in original post

3 REPLIES 3
schaezac
Frequent Visitor

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
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.