Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

wrong total

Hello, my total for "Lost_amount" is shown as average instead of a summary. It is a measure: Lost_amount = VAR Purchase=CALCULATETABLE(VALUES('6_Lost'[Key]), FILTER(ALL('Date'), 'Dat...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 

    Still can be addef to the SUMMARIZE table

    Lost_new =
    SUMX (
    SUMMARIZE(
    '6_Lost'
    '6_Lost'[StartDate],
    '6_Lost'[EndDate],
    '6_Lost'[Product],
    '6_Lost'[CYname],
    'Date'[Jahr],
    'Date'[Monat]
    ),
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi Anonymous 

please try

Lost_amount =
SUMX (
SUMMARIZE (
'6_Lost',
'6_Lost'[StartDate],
'6_Lost'[EndDate],
'6_Lost'[Product],
'6_Lost'[CYname]
),
CALCULATE (
VAR Purchase =
CALCULATETABLE (
VALUES ( '6_Lost'[Key] ),
FILTER (
ALL ( 'Date' ),
'Date'[Date]
>= MIN ( 'Date'[Date] ) - 'Month'[Month Value] * 30
&& 'Date'[Date] <= MAX ( 'Date'[Date] )
)
)
VAR _Current =
CALCULATETABLE (
VALUES ( '6_Lost'[Key] ),
FILTER (
ALL ( 'Date' ),
'Date'[Date] >= MIN ( 'Date'[Date] )
&& 'Date'[Date] <= CALCULATE ( [Min], 'Date'[Date] )
)
)
RETURN
CALCULATE (
CALCULATE (
'6_Lost'[Last_Sales],
DATESBETWEEN (
'Date'[Date],
MIN ( 'Date'[Date] ) - 'Month'[Month Value] * 30,
MIN ( 'Date'[Date] )
)
),
EXCEPT ( Purchase, _Current )
)
)
)

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi, 

 

thanks for your help. I have tried it and add another column "Lost_new", but it seems to be empty 😞

 

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Anonymous 

    Can you please share the code that you have used? What columns are placed in the table visual?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      My fault. Indeed it was not complete. Two columns are in the visual too, which come from a date table:

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Anonymous 

        You need to add these two columns to the SUMMARIZE table