Search code examples
variablespowerbidaxpowerbi-desktop

Store more than one calculation in a variable in Power Bi

Lets assume I have this table and the campaigns is basically a slicer. So if a user selects campaign 1, it will get the total bays of 115 multiply that by the total days of 30, and then lastly dividing the multiplication by the total days. So for that example it will be 3450 divided by 30 which will result back to 115.

Here's where my problem is, lets say the user selects all three campaigns, I then basically need to calculate row by row the bays x days, then the grand total of that to divide it by the total days so you see the expected average should be 114.85 rather than just doing a simple average between all the bays which would give 114.83

enter image description here

Current DAX:

AvgBays v3 = 
VAR bayxDays = [newAverageBays] * [newRangedDays]
VAR totalDays = SUMX(VALUES('Overview Table'[campaign]), [newRangedDays])
RETURN
    COALESCE(CALCULATE (
        DIVIDE(
            SUMX(VALUES('Overview Table'[campaign]), bayxDays),
            totalDays
        ),
        REMOVEFILTERS ('Overview Table'[No Dimension] )
    ),0)

I can manage to get a sum of the days because that just uses SUMX to iterate over the whole column and give me the total which I can store in a variable, but my question is, how would I do the row by row calculation and store that in a variable if more than one campaign has been selected. So it needs to do 115 x 30 store that in a variable, then if campaign 2 is selected, 113.5 x 28 then store that sum into the same variable for me to be able to get the sum of all the multiplications, 9992.

Solution

  • Something like this?

    AvgBays v3 = 
  var totalBayDays =
    SUMX(
      VALUES('Overview Table'[campaign]),
      [newAverageBays] * [newRangedDays]
    )

  var totalDays = 
    SUMX(
      VALUES('Overview Table'[campaign]),
      [newRangedDays]
    )

  return DIVIDE(totalBayDays, totalDays )