Currency Switch Measure is Mixing All Currencies at Row Level

[AdamOrtega]

Hello,

We have a report built where a slicer can select between either US or CA. This is triggering a switch measure to select either the USD$ or CAD$ column from the DB Table:

Sales $ =

        Switch(true(),

        [OnlyCanadaSelected]="No",[Sales (USD)],

        [OnlyCanadaSelected]="Yes",[Sales (Local)])

OnlyCanadaSelected = IF(COUNTROWS(FILTER(FlashSalesSites,FlashSalesSites[Country]<> "CA")) = 0,"Yes","No")

When trouble shooting the model, I see that "Sales $" column shows local currency for both countries due to the row level context BUT the Grand Total for the matrix is totaling in USD$:

I have tried multiple rewrites of the logic but cannot get around the row level. Also, I cannot understand why the Grand Total is correct? I would love some suggestions on how to correct!

Thanks

[amitchandak]

@AdamOrtega , Ideally speaking you should not show grand total for mixed currency or try like

Sales $ =Sumx(Values(Table[Country]) , 
       calculate( Switch(true(),
        [OnlyCanadaSelected]="No",[Sales (USD)],
        [OnlyCanadaSelected]="Yes",[Sales (Local)]) ))

[AdamOrtega]

Yes I agree. I am trying to show only USD$ currency in "Sales $" with the switch measures listed. I cannot seem to remove the row level context however for "CA" which is causing the mixed currency issue. 

Apologies for not being more clear in the orginal post. 

[Anonymous]

Hi @AdamOrtega ,

I think your issue should be caused by [OnlyCanadaSelected] measure.

Due to the row context, in "CA" level, there should be no other countrys <> "CA", so [OnlyCanadaSelected] will return "Yes", [Sales $] will return CAD$

in "US" level, there should be no other countrys = "CA", so [OnlyCanadaSelected] will return "No", [Sales $] will return USD$

in subtotal, there should be all countries from the whole table, so [OnlyCanadaSelected] will return "No", you will get correct total, USD$.

OnlyCanadaSelected = IF(COUNTROWS(FILTER(FlashSalesSites,FlashSalesSites[Country]<> "CA")) = 0,"Yes","No")

New code:

OnlyCanadaSelected =
IF (
    COUNTROWS (
        FILTER ( ALL ( FlashSalesSites ), FlashSalesSites[Country] <> "CA" )
    ) = 0,
    "Yes",
    "No"
)

You can try ALL() function to remove filter from your data model.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AdamOrtega · Accepted solution]

Unfortunately your formula will only give a "NO" result regardless of the filter selection as it is using the COUNTROWS against the entire data set. 

I have solved the issue today using the below logic:

OnlyCanadaSelectedTEST = 
IF (
    Calculate(
            COUNTROWS (
        FILTER ( ALLSELECTED( FlashSalesSites ), FlashSalesSites[Country] <> "CA" ))
    ) = 0,
    "Yes",
    "No"
)

I had to also use this in conjunction with a "Is Not Blank" filter on my "Sites" as there were phantom locations pulling in with no "Country" information.

I appreciate everyone helping me think this through!