sumx but missing total

[sammy02hk]

Hi there,

Thank you for your help. I got 2 table and I would like to sum the Net Sales. I have tried different mearsures, but they either wrong total or missing total.

Table Structure as below:

Table 1 Promotion Period

ArticleCode  SiteCode  End_Date (Measure Caluate sales after EndDate) 

Table 2 Txn Details

ArticleCode SiteCode Billing_Date NET_SALES

M1 = CALCULATE(sum('Table 2'[NET_SALES]),
'Table 2'[BILLING_DATE]>max('Table 1'[End Date])&&'Table 2'[SITE_CODE]=min('Table 1'[Site Code]))

M2:

AfterPeriodSalesM =

VAR a = SELECTEDVALUE('Table 1'[End Date_New])

VAR n = SELECTEDVALUE('Table 1'[Site Code])

RETURN

CALCULATE(SUMx('Table 2',VALUE('Table 2'[NET_SALES]),

FILTER('Table 2','Table 2'[BILLING_DATE]>a

&& 'Table B'[SITE_CODE]=n))

M3 =

SUMX(

VALUES('Mapping List'[ARTICLE_CODE]),'Mapping List'[M2]) 

Thank you for your help. 

[tamerj1 · Accepted solution]

Hi @sammy02hk 

please try

M1 =
SUMX (
SUMMARIZE (
'Table1',
'Table1'[Article_Code],
'Table1'[SiteCode],
'Table1'[End Date_New]
),
VAR a = 'Table 1'[End Date_New]
VAR n = 'Table 1'[SiteCode]
RETURN
CALCULATE (
SUM ( 'Table 2'[NET_SALES] ),
'Table 2'[BILLING_DATE] > a
&& 'Table 2'[SITE_CODE] = n
)
)

AfterPeriodSalesM =
SUMX (
SUMMARIZE (
'Table1',
'Table1'[Article_Code],
'Table1'[SiteCode],
'Table1'[End Date_New]
),
VAR a = 'Table 1'[End Date_New]
VAR n = 'Table 1'[SiteCode]
RETURN
CALCULATE (
SUMX ( 'Table 2', VALUE ( 'Table 2'[NET_SALES] ) ),
FILTER ( 'Table 2', 'Table 2'[BILLING_DATE] > a && 'Table B'[SITE_CODE] = n )
)
)

[sammy02hk]

Hi It works.

I have try different way to use summarize as well. But it didn't work out. 

Lets me study how to embed this...

Thank you very much