Problem With SUMX Filters

[Ewo]

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)

,1

,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

[harshnathani]

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

[Ewo]

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;

    

[harshnathani · Accepted solution]

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

[Ewo]

Thank you for your support @harshnathani

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?

[harshnathani]

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