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

      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?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

         

         

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