topic Re: wrong total in DAX Commands and Tips

wrong total

Anonymous — Sat, 25 Feb 2023 19:01:57 GMT

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

Re: wrong total

tamerj1 — Sat, 25 Feb 2023 19:28:16 GMT

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

Re: wrong total

Anonymous — Sat, 25 Feb 2023 19:38:13 GMT

Hi,

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

Re: wrong total

tamerj1 — Sat, 25 Feb 2023 19:52:52 GMT

Anonymous

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

Re: wrong total

Anonymous — Sat, 25 Feb 2023 19:56:13 GMT

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

Re: wrong total

tamerj1 — Sat, 25 Feb 2023 20:02:37 GMT

Anonymous

You need to add these two columns to the SUMMARIZE table

Re: wrong total

Anonymous — Sat, 25 Feb 2023 20:17:31 GMT

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.

Re: wrong total

tamerj1 — Sat, 25 Feb 2023 20:40:43 GMT

Anonymous

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

Re: wrong total

Anonymous — Sat, 25 Feb 2023 20:55:29 GMT

sure, here you go:

Re: wrong total

tamerj1 — Sat, 25 Feb 2023 21:01:39 GMT

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]

Re: wrong total

Anonymous — Sat, 25 Feb 2023 21:26:31 GMT

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 🙂

Re: wrong total

anushaghi123 — Wed, 13 Sep 2023 10:16:51 GMT

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

Re: wrong total

tamerj1 — Wed, 13 Sep 2023 17:18:02 GMT

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

Re: wrong total

anushaghi123 — Thu, 14 Sep 2023 07:01:52 GMT

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