Forum Discussion

Ewo's avatar
Ewo
Frequent Visitor
6 years ago
Solved

Problem With SUMX Filters

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

 

 
 

 

  • Anonymous's avatar
    Anonymous
    6 years ago

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Ewo
      Frequent Visitor
      6 years ago

      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;

          

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

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