topic Re: DAX - Total Value of a measure is not correct in DAX Commands and Tips

DAX - Total Value of a measure is not correct

Anonymous — Tue, 21 Mar 2023 10:50:22 GMT

Hi guys,

I need your help please.

Based on the following sample :

Supplier	Product	date	Sales
TOTO	prd1	28/02/2023	69,785
TOTO	prd2	28/02/2023	94,4
TOTO	prd6	14/02/2023	140
TOTO	prd7	03/02/2023	2194,5312
TOTO	prd8	03/02/2023	83,1528
TOTO	prd9	18/01/2023	246,1275
TOTO	prd10	18/01/2023	279,7375
TOTO	prd14	16/01/2023	477,5
TOTO	prd17	19/12/2022	311,4475
TOTO	prd18	19/12/2022	749,825
TATA	prd1	28/02/2023	8150
TATA	prd5	27/02/2023	2142,5
TATA	prd6	27/02/2023	1467,5
TATA	prd8	23/02/2023	9619,675
TATA	prd9	21/02/2023	775
TATA	prd10	16/02/2023	1885,71375
TATA	prd13	14/02/2023	1355
TATA	prd14	08/02/2023	5450
TATA	prd15	08/02/2023	2749,955
TATA	prd20	01/02/2023	2359,9895
TATA	prd21	31/01/2023	470
TATA	prd23	31/01/2023	750
TATA	prd26	25/01/2023	190
TATA	prd27	25/01/2023	920
TATA	prd28	23/01/2023	687,5
TATA	prd29	23/01/2023	1030
TATA	prd33	19/01/2023	2469,989
TATA	prd34	19/01/2023	6473,48
TATA	prd35	12/01/2023	5883,44
TATA	prd36	05/01/2023	4669,958
TATA	prd37	27/12/2022	575
TATA	prd38	27/12/2022	325
TATA	prd40	20/12/2022	946
TATA	prd45	13/12/2022	2027,399
TATA	prd46	02/12/2022	5883,455

I have created a measure :

Sales 3RM =

var _currentDate = MAX('Calendar'[Date])

return

CALCULATE(SUM(Data_Sample[Sales]),

FILTER(ALLSELECTED('Calendar'),AND('Calendar'[Date]<= _currentDate , dateadd('Calendar'[Date],3,MONTH)>_currentDate)))

so I can see dynamically the rolling 3 last months sales for each supplier which works for each row.

However, the total shown is not correct, I should have 120 087,04.

Plus with the correct total (which I want to display on each row), I will be able to calculate the proportion of Sales 3RM per supplier on total Sales 3RM.

I tried to use a SUMX with a Summarize table but without any success.

Thank you !

Re: DAX - Total Value of a measure is not correct

tamerj1 — Tue, 21 Mar 2023 11:02:12 GMT

Hi Anonymous
Please try

Sales 3RM = SUMX ( SUMMARIZE ( Data_Sample, Data_Sample[Supplier], 'Calendar'[YearMonth] ), VAR _currentDate = CALCULATE ( MAX ( 'Calendar'[Date] ) ) RETURN CALCULATE ( SUM ( Data_Sample[Sales] ), FILTER ( ALLSELECTED ( 'Calendar' ), AND ( 'Calendar'[Date] <= _currentDate, DATEADD ( 'Calendar'[Date], 3, MONTH ) > _currentDate ) ) ) )

Re: DAX - Total Value of a measure is not correct

ValtteriN — Tue, 21 Mar 2023 11:03:33 GMT

Hi,

This is a common question. Basically what is happening is that the total row calculates the dax you are using but with "empty" filter context. I recommend reading this article: Obtaining accurate totals in DAX - SQLBI

One of the more common solutions I use is to check for total row filter conext and use different calculation logic there e.g. IF(
isblank(MAX('table'[supplier])), [total row measure],
[normal measure])

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: DAX - Total Value of a measure is not correct

Anonymous — Tue, 21 Mar 2023 13:54:55 GMT

Thank you very much for your response tamerj1

But what I really need is to have the total "Sales 3RM" (63 084.93 for the selection made in the following exemple) for each supplier on each row.

For exemple :

The result expected is in the last column.

Did you know how to do this ?

Sorry, I should have been more precise in my explanation.

Re: DAX - Total Value of a measure is not correct

tamerj1 — Tue, 21 Mar 2023 14:01:40 GMT

Anonymous
Please try

Sales 3RM = SUMX ( CALCULATETABLE ( SUMMARIZE ( Data_Sample, Data_Sample[Supplier], 'Calendar'[YearMonth] ), ALLSELECTED ( Data_Sample[Supplier] ) ), VAR _currentDate = CALCULATE ( MAX ( 'Calendar'[Date] ) ) RETURN CALCULATE ( SUM ( Data_Sample[Sales] ), FILTER ( ALLSELECTED ( 'Calendar' ), AND ( 'Calendar'[Date] <= _currentDate, DATEADD ( 'Calendar'[Date], 3, MONTH ) > _currentDate ) ) ) )

Re: DAX - Total Value of a measure is not correct

Anonymous — Tue, 21 Mar 2023 14:10:45 GMT

I tried this measure tamerj1 :

But it's not the result I expect.

What I want is the following result (last column) :

It's the total sum of the 3rd column obtained by the measure :

Sales 3RM =

var _currentDate = MAX('Calendar'[Date])

return

CALCULATE(SUM(Data_Sample[Sales]),

FILTER(ALLSELECTED('Calendar'),AND('Calendar'[Date]<= _currentDate , dateadd('Calendar'[Date],3,MONTH)>_currentDate)))

Re: DAX - Total Value of a measure is not correct

tamerj1 — Tue, 21 Mar 2023 14:30:42 GMT

Anonymous
I have no idea what does the result that you have obtained using my dax represent 😂

Please try

Sales 3RM = VAR _currentDate = MAX ( 'Calendar'[Date] ) RETURN CALCULATE ( SUM ( Data_Sample[Sales] ), FILTER ( ALLSELECTED ( 'Calendar' ), AND ( 'Calendar'[Date] <= _currentDate, DATEADD ( 'Calendar'[Date], 3, MONTH ) > _currentDate ) ), ALLSELECTED ( Data_Sample[Supplier] ) )

Re: DAX - Total Value of a measure is not correct

Anonymous — Tue, 21 Mar 2023 14:43:08 GMT

tamerj1

I will try to be more precise ^^

Below, in the 3rd column is the result of the measure you just gave me ("Sales 3RM") :

But What I need is in the color red, in the last column which is the total of each value of the measure you just gave me.

Re: DAX - Total Value of a measure is not correct

tamerj1 — Tue, 21 Mar 2023 14:55:16 GMT

Anonymous

I understand what is your expected results. The matter is that I wasn't able to successfully obtain it so far.
The [Supplier] column that you are using in the table visual is from which table?

Re: DAX - Total Value of a measure is not correct

Anonymous — Tue, 21 Mar 2023 14:57:44 GMT

Ok tamerj1

The supplier column comes from a Supplier table I have created using DAX :

Re: DAX - Total Value of a measure is not correct

Anonymous — Tue, 21 Mar 2023 15:17:55 GMT

tamerj1

Your question give me the answer !

I replace the value on the ALLSELECTED function by the Supplier in the table Supplier and it works !!

Total Sales 3RM =

VAR _currentDate =

MAX ( 'Calendar'[Date] )

RETURN

CALCULATE (

SUM ( Data_Sample[Sales] ),

FILTER (

ALLSELECTED ( 'Calendar' ),

AND (

'Calendar'[Date] <= _currentDate,

DATEADD ( 'Calendar'[Date], 3, MONTH ) > _currentDate

)

ALLSELECTED ( Supplier[Supplier])

)

Thank you very much 😉

Have a nice day.

Re: DAX - Total Value of a measure is not correct

tamerj1 — Tue, 21 Mar 2023 15:25:06 GMT

Anonymous

Exactly