Forum Discussion

sammy02hk's avatar
sammy02hk
Icon for Helper I rankHelper I
3 years ago
Solved

sumx but missing total

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's avatar
    tamerj1
    3 years ago

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

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      sammy02hk
      Icon for Helper I rankHelper I
      3 years ago

      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