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'),
'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]) //-'Month'[Month Value]*30 &&
'Date'[Date] <= [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))


I am not sure how I can fix this. Does anyone has an idea?

Thanks

 

  • 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]
    ),

13 Replies

  • 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?