Forum Discussion

mtc08's avatar
mtc08
New Member
3 years ago

Power BI Matrix Count Columns and Populate Blank Values for Budget Actuals Financial Review

I am working on a financial analysis file -- there are "Budget" and "Data" tables.  What I want to show is the cumulative over or under budget by week in a matix.  Measures are calculated as such and shown as columns under each week:

  • _Actuals = sum(Data[Total Line]).  This is the Actuals Spent column.
  • _ColumnCount = sumx(values(Data[Week]), if ([_Actuals]<>-99999,1,0))
  • _VarianceToBudget = SUM('Budget-tbd'[Per Week (Budget)])*[_ColumnCount] - [_Actuals].  This is the Good/(Bad) column.

I get pretty close.  But in the screenshot there are a few issues:

  • Groceries work good: it shows under budget of $100 in the first week and $50 for the second week for a total +$150 under budget in last line.
  • Laundry show over budget -$35.07 in first week, no activity in second week, and -$35.07 total.  However, I want to take the two weeks for budget and subtract the one week of actuals, i.e., $4 + $4 - $35.07 = -$31.07.
  • Lunch - school is similar to Laundry.  It shows -$70 over budget based on one week of activity.  However, I want to take the two weeks for budget and subtract the one week of actuals, i.e., $20 + $20 - $90 = -$50.00.
  • Telephone -- there are no actuals so it is not showing up.  However, I want to show that we are under budget because we didn't spend, i.e., $43.85 + $43.85 - $0 spent = +$87.70.

 

 

No RepliesBe the first to reply