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 ,

 

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