Forum Discussion
Matrix Column Subtotal showing incorrect amount
Hi,
Could you share the download link of the PBI file?
It looks like you are summing all of [Revenue (Base)] without consideration of time/weeks dimension, whether you display numbers as "" is irrelevant to the dax total. The total is not the sum of the cells that preceed it, it is the total absent the columns filter context.
Try something like:
CALCULATE(sum(Data[Revenue (Base)]), FILTER(Data, Data[Category] <> "Won" && Data[Category] <> "Lost"), Calendar_data[Week No] >=TDDateWeekNo)
Basically, you need to add a calculate term that filters down the Calander table to the period you want included in the sum.
Also - it would be fine to write the last measure as if(flag, CurrentRevenue) - if this is not true it will return blank() by default - but if you define your calculate properly it shouldn't be needed because prior cells will have no value.
Here you go
ModifiedRevenue =
//Determine the quarter week no
VAR TDDateWeekNo=[TodaysDate]
Var rowfiltercontext=max(Calendar_Data[Week No])
Var CurrentRevenue=
CALCULATE(
sum(Data[Revenue (Base)]),
FILTER(Data,Data[Category] <>"Won"),
FILTER(Data,Data[Category]<> "Lost"))
var lastrevenue= ""
var flag=TDDateWeekNo<=rowfiltercontext
return
if(flag,CurrentRevenue,lastrevenue)
It looks like you are summing all of [Revenue (Base)] without consideration of time/weeks dimension, whether you display numbers as "" is irrelevant to the dax total. The total is not the sum of the cells that preceed it, it is the total absent the columns filter context.
Try something like:
CALCULATE(sum(Data[Revenue (Base)]), FILTER(Data, Data[Category] <> "Won" && Data[Category] <> "Lost"), Calendar_data[Week No] >=TDDateWeekNo)
Basically, you need to add a calculate term that filters down the Calander table to the period you want included in the sum.
Also - it would be fine to write the last measure as if(flag, CurrentRevenue) - if this is not true it will return blank() by default - but if you define your calculate properly it shouldn't be needed because prior cells will have no value.
That solution worked well. Thank you!