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 b...
  • 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 )
    )
    )

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