Forum Discussion

Frequent Visitor

3 years ago

Solved

Need help on filter dax

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

FreemanZ
3 years ago
hi Hong_HW
Try like:
Measure =
IF(
SELECTEDVALUE(Table1[Country]) = "CTY",
SUMX(ALL(Table2),Table2[USD]),
SUM(Table2[USD])
)

6 Replies

FreemanZ
Super User
3 years ago
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
  Frequent Visitor
  3 years ago
  oh, yes there is typo , TH is actually PH.
  CTY means for all countries sum up
Hong_HW
Frequent Visitor
3 years ago
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
  Super User
  3 years ago
  hi Hong_HW
  Try like:
  Measure =
  IF(
  SELECTEDVALUE(Table1[Country]) = "CTY",
  SUMX(ALL(Table2),Table2[USD]),
  SUM(Table2[USD])
  )
  - Hong_HW
    Frequent Visitor
    3 years ago
    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)
    )

Related content