Forum Discussion

Aruljoy's avatar
Aruljoy
Helper II
6 years ago
Solved

incorrect Column Total

 

Matrix

 

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 get the total for only 5 weeks in this visual. can you please help me to fix this?

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.

6 Replies

  • 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]))
  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    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.