Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AdamOrtega
Frequent Visitor

Currency Switch Measure is Mixing All Currencies at Row Level

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$:

 

AdamOrtega_1-1659383430895.png

 

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

 

 

 

 

 

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

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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)]) ))

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. 

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.

 

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!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors