Forum Discussion

schaezac's avatar
schaezac
Frequent Visitor
1 year ago
Solved

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 ...
  • schaezac's avatar
    schaezac
    1 year ago

    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's avatar
bhanu_gautam
Super User
1 year ago

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's avatar
    schaezac
    Frequent Visitor
    1 year ago

    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!