Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Matrix Column Subtotal showing incorrect amount

Hi guys,

Below is a matrix with 1-13 as the week. I've written a measure to hide W1-W10 as those weeks have passed. However,
my matrix visual is still summing up the previous weeks. 

Would appreciate any help or feedback on this

|

Regards,

 




Need Help
  • Alexrai's avatar
    Alexrai
    4 years ago

    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.

8 Replies

  • themistoklis's avatar
    themistoklis
    Community Champion
    4 years ago

    Anonymous 

    Can you please share the formula that you used in the measure?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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)

       

      • Alexrai's avatar
        Alexrai
        Helper I
        4 years ago

        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.

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi Anonymous ，

     

    According to the BLANK() function, we can find that blank values and empty strings are not always equivalent. Try to replace lastrevenue= "" with lastrevenue= BLANK(), which will be able to hide the column of blank values correctly.

     

    Measure = //Determine the quarter week no
VAR TDDateWeekNo=[TodaysDate]
Var rowfiltercontext=max(Calender[WeekNo])


Var CurrentRevenue=
CALCULATE(
sum(Data[Revenue]),
FILTER(Data,Data[Category] <>"Won"),
FILTER(Data,Data[Category]<> "Lost"))



var lastrevenue= BLANK()
var flag=TDDateWeekNo<=rowfiltercontext
return
if(flag,CurrentRevenue,lastrevenue)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.