Forum Discussion

Aruljoy's avatar
Aruljoy
Helper II
6 years ago
Solved

incorrect Column Total

    The column total is incorrect in Matrix Visual. The month measure has 9 weeks. The weeks after w5 are filtered at visual level. The current total is calculated for all 9 weeks. I want to ...
Need Help
  • Aruljoy's avatar
    Aruljoy
    6 years ago

    I am getting memory issue if use sumx function. "There is not enough memory to complete the operation.

  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Aruljoy 

     

    Based on your description, I create data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create two calculated columns and a measure as below.

    Calculated column:
Month = FORMAT('Table'[Date],"mmm")
Weeknum = "W"&WEEKNUM('Table'[Date])

Measure:
Result = 
IF(
    ISINSCOPE('Table'[Weeknum]),
    SUM('Table'[Value]),
    CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Month] = SELECTEDVALUE('Table'[Month])))
)

     

    Result:

     

    Best Regards

    Allan

     

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

az38's avatar
az38
Community Champion
6 years ago

Hi Aruljoy 

you need to create a measure like

Measure = SUM(Table[Value])

and put it into visual as value

Aruljoy's avatar
Aruljoy
Helper II
6 years ago

Thanks for the response az38 . But the value is already a measure. 

Num_Closure =
Var _selected = SELECTEDVALUE('Cut Points1'[Measures])
Var _Result =[Num_by_Week] - [Num_by_PrevWeek]
Return
IF(_selected in {"OMW","CTM","AnG"},"NA",_Result)
Can you please let me know how to handle this?
  • az38's avatar
    az38
    Community Champion
    6 years ago

    Aruljoy 

    how do look like your [Num_by_Week] and [Num_by_PrevWeek] measures?

    also try

    Var _Result = CALCULATE(SUMX(Table, [Num_by_Week] - [Num_by_PrevWeek]))
    • Aruljoy's avatar
      Aruljoy
      Helper II
      6 years ago

      I am getting memory issue if use sumx function. "There is not enough memory to complete the operation.