wrong total

[lost_flower]

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]

Hi @lost_flower 

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 )
)
)
)

[lost_flower]

Hi, 

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

[tamerj1]

@lost_flower 

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

[lost_flower]

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

[tamerj1]

@lost_flower 

You need to add these two columns to the SUMMARIZE table

[lost_flower]

Do you mean in the SUMMARIZE code? 

Lost_new =

SUMX (

SUMMARIZE ('Date','Date'[Jahr],'Date'[Month_txt],

'6_Lost'

'6_Lost'[StartDate],

'6_Lost'[EndDate],

'6_Lost'[Product],

'6_Lost'[CYname]

),

These two dates comes from another table and I cannot add them. two tables in summarize code, is this possible?
Sorry, I am still learning DAX and I think I have add it wrong, because the code is not working. 

[tamerj1]

@lost_flower 

Ok can you olease share a screenshot of the data model view to see the relationships.

[lost_flower]

sure, here you go:

[tamerj1 · Accepted solution]

@lost_flower 

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]

),

[anushaghi123]

Hi  @tamerj1 ,

I have created the quick measure to calculate the moving average with dynamic slicer. But facing column total error for the measure. Like it has to sum Jan to Dec data and show it in the column but column total only taking Dec month data everytime.

Example - Should be Oct+Nov+Dec = 75379+62685+26346 = 164410 But the total showing is 26346.

Please help me with the following issue

I am getting column total error and taking Matrix table 

Rows - Country,ProductCategory

Column - Month

I'm getting column total incorrect for Forecast(Blue) data.

Dax Formula (Quick Measure) - Product_Count rolling average =
IF(
ISFILTERED('Table_name'[dimdate]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Table_name'[dimdate].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Table_name'[dimdate].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, 'Moving Average'[Moving Average Value], MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Table_name'),
'Table_name'[dimdate].[Year],
'Table_name'[dimdate].[QuarterNo],
'Table_name'[dimdate].[Quarter],
'Table_name'[dimdate].[MonthNo],
'Table_name'[dimdate].[Month]
),
__DATE_PERIOD
),
CALCULATE(
SUM('Table_name'[Product_Count]),
ALL('Table_name'[dimdate].[Day])
)
)
)

Thanks

[tamerj1]

Hi @anushaghi123 

please try

Product_Count rolling average correct total =
SUMX (
SUMMARIZE (
'Table_name',
'Table_name'[dimdate].[Year],
'Table_name'[dimdate].[QuarterNo],
'Table_name'[dimdate].[Quarter],
'Table_name'[dimdate].[MonthNo],
'Table_name'[dimdate].[Month]
),
[Product_Count rolling average]
)

[anushaghi123]

Thanks so much @tamerj1 . It is working but now only showing till current month not the forecast data for October month like below.

[lost_flower]

Oh, wow. I did not suspect that it would make a difference by putting it at the end. It works now. 

Thanks so much for your help. Really appreciate it 🙂