DAX - Total Value of a measure is not correct

[Mohamed_59]

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 !

[tamerj1 · Accepted solution]

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

[ValtteriN]

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/

[tamerj1]

Hi @Mohamed_59 
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
                )
            )
        )
)

[Mohamed_59]

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.

[tamerj1]

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

[Mohamed_59]

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

[tamerj1 · Accepted solution]

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

[Mohamed_59]

@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.

[tamerj1]

@Mohamed_59 

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?

[Mohamed_59]

Ok @tamerj1 

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

[Mohamed_59 · Accepted solution]

@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.

[tamerj1]

@Mohamed_59 

Exactly