Need help on filter dax

[Hong_HW]

Hi,

I'll need some help on the dax formula.

I have 2 tables below

Table 1 named Country with 2 columns

Country rollup	Hub
CTY	CTY
TH	ASEAN
MY	ASEAN
CN	NASIA
HK	NASIA

Table 2 named Sales Amt

Country	USD
PH	300
MY	200
CN	150
HK	200

In my PBI, i have the country as the filter, now i want to create a table to return the corresponding sales amount by selecting the options from the country filter.

i.e Select CTY, return 850 ( which is the sum of SG+MY+CN+HK)

    Select ASEAN, result table return 500 (which is the sum of PH+MY)

    Select NASIA, result table return 350 (which is the sum of CN+HK)

    Select PH, only return 300;

Much appreciate it.

Thank you

Regards

Hazel

[Hong_HW]

Some add on information

Table 1 

Table 2

Currently when i select the individual country, the sales amount is showing up.

But when i select CTY, the sales amount is showing blank.  I'm not sure if there is any dax could help me to sum up the sales amount from all countries? Much appreciate it.

[FreemanZ · Accepted solution]

hi @Hong_HW 

Try like:

Measure =

IF(

   SELECTEDVALUE(Table1[Country]) = "CTY",

   SUMX(ALL(Table2),Table2[USD]),

   SUM(Table2[USD])

)

[Hong_HW]

Thank you very much, it works 👍.

I've tried the following formula, but it doesn't work. Not sure what is the issue.  It will be much appreated if you could advise too?

Test Measure 1 =
VAR SelectedCountry = SELECTEDVALUE('Table 1'[Country rollup])
VAR SelectedHub = SELECTEDVALUE('Table 1'[Hub])

RETURN
IF(
SelectedHub = "CTY",
CALCULATE(
SUM('Table 2'[Amount]),'Table 2'[Country] in {"PH","MY","CN","HK"}),

CALCULATE(
SUM('Table 2'[Amount]),
'Table 2'[Country] = SelectedCountry)
)

[FreemanZ]

hi @Hong_HW 

it seems, this part:

SelectedHub = "CTY",
CALCULATE(
SUM('Table 2'[Amount]),'Table 2'[Country] in {"PH","MY","CN","HK"})

will return blank, as there is no row where hub is CTY and country is any of "PH","MY","CN","HK".

[FreemanZ]

hi @Hong_HW 

in table1, is TH actually PH?

What does CTY mean? (The others stand for countries or regions)

ASEAN and NASIA are not countries, so they could not in the same column as PH, right?

[Hong_HW]

oh, yes there is typo , TH is actually PH.

CTY means for all countries sum up