Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
3 years ago
Solved

Incorrect Totals - Matrix

Hi, 

I have the following measure which works as expected however, the grand totals don't show the expected value.

 

I have tried to add in HASONEVALUE, SUMX and create new measures on the back of this, but whatever I try, I just can't work out how to correctly do it. Would anyone know how to amend my current logic to get this to work or, a separate measure based on this logic please? 

Measure = 
  VAR __AppStatus = MAX('apps'[app_status])
  VAR __IsPriorMonth = MAX('month_index'[is_prior_month])
  VAR __AppValue = MAX('apps'[app_value])
  VAR __Result =
    SWITCH( TRUE(),
      __AppStatus = "live" && __IsPriorMonth = "yes", 0,
      ( __AppStatus = "test" && __IsPriorMonth = "yes" ) ||
        ( __AppStatus = "test" && __AppValue < 30 ), 0,
      SUM('apps'[amount])
    )
RETURN
  __Result

 

As an example, I have have this total below as 50, but based on the output, I should have 20. It seems like the total is ignoring the logic and just taking the total regardless l.

 

          Jan Feb Mar Total

Live    0.    10.   10.  50

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  M_SBS_6 ,

    I created some data:

    Here are the steps you can follow：

     

    1. Create measure.

     

    Measure2 =
var _table1=
SUMMARIZE(
    'Table','Table'[Group],"Value1",[Measure])
var _table2=
SUMMARIZE(
    'Table','Table'[Group2],"Value2",[Measure])
return
IF(
    NOT(HASONEVALUE('Table'[Group2])),SUMX(_table2,[Value2]),
    IF(
        NOT(HASONEVALUE('Table'[Group])),SUMX(_table1,[Value1]),[Measure])
)

     

    2. Result:

     

    Best Regards,

    Liu Yang

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

4 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    write another measure like this
    SUMX(VALUES('Date'[MonthName],[Measure])

    • M_SBS_6's avatar
      M_SBS_6
      Helper V
      3 years ago

      Thanks for your suggestion. I have tried that but get thr following error message 

       

      The syntax for 'RETURN' is incorrect 

      Not too sure why? 

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    what kind of return, I asked to write

    = SUMX(VALUES('Date'[MonthName],[Measure])
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  M_SBS_6 ,

    I created some data:

    Here are the steps you can follow：

     

    1. Create measure.

     

    Measure2 =
var _table1=
SUMMARIZE(
    'Table','Table'[Group],"Value1",[Measure])
var _table2=
SUMMARIZE(
    'Table','Table'[Group2],"Value2",[Measure])
return
IF(
    NOT(HASONEVALUE('Table'[Group2])),SUMX(_table2,[Value2]),
    IF(
        NOT(HASONEVALUE('Table'[Group])),SUMX(_table1,[Value1]),[Measure])
)

     

    2. Result:

     

    Best Regards,

    Liu Yang

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