topic Re: Problem With SUMX Filters in DAX Commands and Tips

Problem With SUMX Filters

Ewo — Mon, 11 May 2020 13:02:51 GMT

Hi,

I have some problem with SUMX function. I want to sum usd amount (Discount / USDRate) when i select to different countries with different currency. So...

I have data like this :

And i have two measures that i use;

1-)

DiscountTotal = SUMX(OrderTable

,OrderTable[Discount] / IF(

HasOneValue(CountryID)

,OrderTable[USDRate]

)

2-) one measure for DiscountGroup = "Coupon" and one measure for DiscountGroup = "Referral"

CouponDiscount = CALCULATE(

[DiscountTotal]

,FILTER(

OrderTable

,OrderTable[DiscountGroup] = "Coupon" //"Referral"

)

My problem is; I select 2 countries with different currencies, calculate works fine for "Referral" because filtered OrderTable has both country values. But it doesn't work for "Coupon" because filtered OrderTable (DiscountGroup filter) has just one value for country so it doesn't make calculation.

I tried to use HASONEVALUE(CountryId) with multiple different ways but result didn't change.

Is there any suggestion?

Thanks

Re: Problem With SUMX Filters

Anonymous — Tue, 12 May 2020 05:51:07 GMT

Hi Ewo ,

You can create teh following measures.

Total Discount = SUM(OrderTable[Discount])

Total USD Rate = SUM(OrderTable[USD Rate])

Discount Total = SUMX (OrderTable,DIVIDE([Total Discount],[Total USD Rate]))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Re: Problem With SUMX Filters

Ewo — Tue, 12 May 2020 10:45:14 GMT

Hi harshnathani,

Thank you for reply but i don't want to convert all to USD currency. what i wanna do is; to make usd conversion if selected countries has different currencies.

To be clear, if i select only Spain in filter, it will be local currency (EUR) in table.
If i select Spain and England, it will be USD currency.

Result must be like this;

Re: Problem With SUMX Filters

Anonymous — Tue, 12 May 2020 12:05:48 GMT

Hi Ewo ,

Thanks for the clarifications.

Please use below measures.

Values =

Var __DistinctCountofCountries = CALCULATE(COUNTROWS(VALUES('DIM-Country'[Currency])),ALLSELECTED('DIM-Country'[Name]))

return

IF (__DistinctCountofCountries > 1 , SUMX(OrderTable,(DIVIDE(SUM(OrderTable[Discount]),SUM(OrderTable[USD Rate])))), SUM(OrderTable[Discount]))

Pull Data in a Matrix Visual.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Re: Problem With SUMX Filters

Ewo — Wed, 13 May 2020 11:26:29 GMT

Thank you for your support Anonymous

That worked well and i think i understand the problem in my measures. Filtering sequence is a little confusing for me 🙂

Please correct me if i wrong;

For exmp. if i use 3 different function (dax exprasion) with filters. It starts filtering from outside right?

It's like;

Calculate1(

Calculate2(

Calculate3(

Expression,

Filter3

)

Filter2

)

Filter1

)

In that scneario Filtering starts with Filter1 and goes inside right?

Re: Problem With SUMX Filters

Anonymous — Wed, 13 May 2020 11:34:21 GMT

Hi Ewo ,

_DisctinctCountofCountries find the Disticnt Count of Country Currencies.

So incase you select Italy and Spain, Distinct Count of currency is 1 (EUR).

When you select England and Spain Disctinct Count of currency is 2 (EUR, GBP)

Post that I check if Values is greater than 1, then convert it in USD terms, else let it be in the local currency.

Values =

Var __DistinctCountofCountries = CALCULATE(COUNTROWS(VALUES('DIM-Country'[Currency])),ALLSELECTED('DIM-Country'[Name]))

return

IF (__DistinctCountofCountries > 1 , SUMX(OrderTable,(DIVIDE(SUM(OrderTable[Discount]),SUM(OrderTable[USD Rate])))), SUM(OrderTable[Discount]))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!