Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Error on Switch measure formula

Hi All,

I'm trying to visualise a line graph with count of sales over time (month) between two countries A& B and the Total Sales count(Country A+B). So the Idea is to have a slicer with Country A, Country B and the Total. When a user selects for example Country A it will display the count of sales for that particular country and the Total(two Line Graphs). Then when a user selects total it shows all Country A & B then a (total count for Country A&B)- Three Line graphs. This should be in one visual chart.

I have tried making a control table with the following then creating a relationship with the country. However, I seem to get an error when I select on the total after creating the following measure;
CountyID = MIN[Country[ID])

 

Selected Country = SWITCH([Country ID],
1, [Measure created with count sales "Country A"],

2, [Measure created with count sales "Country B"],

3, [Count County A] + [Count County B]

 

Type ID
A1
B2
Total 3

 

Please help 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I suggest having a disconnected slicer table like below.

    Please check the below picture and the attached pbix file.

     

     

    QTY total: =
    VAR selectedcountry =
    VALUES ( 'Country Slicer'[Country] )
    VAR unselectedcountry =
    EXCEPT ( VALUES ( Country[Country] ), selectedcountry )
    RETURN
    SWITCH (
    SELECTEDVALUE ( 'Country Slicer'[ID] ),
    3,
    SWITCH (
    SELECTEDVALUE ( Country[ID] ),
    3, CALCULATE ( SUM ( Sales[Qty] ), ALL ( country ) ),
    SUM ( Sales[Qty] )
    ),
    SWITCH (
    SELECTEDVALUE ( Country[ID] ),
    3, CALCULATE ( SUM ( Sales[Qty] ), ALL ( country ) ),
    CALCULATE (
    SUM ( Sales[Qty] ),
    KEEPFILTERS ( NOT ( Country[Country] IN unselectedcountry ) )
    )
    )
    )

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I suggest having a disconnected slicer table like below.

    Please check the below picture and the attached pbix file.

     

     

    QTY total: =
    VAR selectedcountry =
    VALUES ( 'Country Slicer'[Country] )
    VAR unselectedcountry =
    EXCEPT ( VALUES ( Country[Country] ), selectedcountry )
    RETURN
    SWITCH (
    SELECTEDVALUE ( 'Country Slicer'[ID] ),
    3,
    SWITCH (
    SELECTEDVALUE ( Country[ID] ),
    3, CALCULATE ( SUM ( Sales[Qty] ), ALL ( country ) ),
    SUM ( Sales[Qty] )
    ),
    SWITCH (
    SELECTEDVALUE ( Country[ID] ),
    3, CALCULATE ( SUM ( Sales[Qty] ), ALL ( country ) ),
    CALCULATE (
    SUM ( Sales[Qty] ),
    KEEPFILTERS ( NOT ( Country[Country] IN unselectedcountry ) )
    )
    )
    )