Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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 =
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 =
@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.
Proud to be a Super User! |
|
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!
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |