Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Slicer by columns name

Hello,   I have the following table:     Sale_ID Unity_Price Tax_1 Tax_2 Tax_3 Tax_4 1 10 5 15 8 4 2 40 30 25 8 7 To calculate the final price of a sale I created a...
Need Help
Show and Tell
Tutorial Requests
  • MFelix's avatar
    MFelix
    9 years ago

    Hi Anonymous,

     

    I would solve this using a measure do the following:

    1. Create a table (do not related this with any other tables with the following structure:
      1. Name Slicer - Selection: Unity_Price, Tax_1, Tax_2, Tax_3, Tax_4
    2. Add the following measure to your data table:
    3. Final_Price =
VAR Unity_price =
    IF (
        CONTAINS ( Slicer, Slicer[Selection], "Unity_Price" ) = TRUE (),
        SUM ( Sales[Unity_Price] ),
        0
    )
VAR Tax1 =
    IF (
        CONTAINS ( Slicer, Slicer[Selection], "Tax_1" ) = TRUE (),
        SUM ( Sales[Tax_1] ),
        0
    )
VAR Tax2 =
    IF (
        CONTAINS ( Slicer, Slicer[Selection], "Tax_2" ) = TRUE (),
        SUM ( Sales[Tax_2] ),
        0
    )
VAR Tax3 =
    IF (
        CONTAINS ( Slicer, Slicer[Selection], "Tax_3" ) = TRUE (),
        SUM ( Sales[Tax_3] ),
        0
    )
VAR Tax4 =
    IF (
        CONTAINS ( Slicer, Slicer[Selection], "Tax_4" ) = TRUE (),
        SUM ( Sales[Tax_4] ),
        0
    )
RETURN
    Unity_price + Tax1
        + Tax2
        - Tax3
        - Tax4
    4. Now just add the valuies from the table you created to a slicer and your measure to the table should give the expected result:

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

Hi Anonymous,

 

When you use measure they are calculated based on context so you cannot make the calculations based on a single value from a colum, meaning that when you have 

VAR On_Time =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "On Time" ) = TRUE (),
Divide( 'Volvo ETA'[On-Time], 'Volvo ETA'[Total], Blank()),
0
)

You should have something like this:

 

VAR On_Time =
IF (
    CONTAINS ( Slicer, Slicer[V_Selection], "On Time" ) = TRUE (),
    DIVIDE ( SUM ( 'Volvo ETA'[On-Time] ), SUM ( 'Volvo ETA'[Total] ), BLANK () ),
    0
)

In this way you are aggregating the values, however be carefull because this can sometimes based on context not return the expected result.

 

Can you please share some sample data and show the expected result in that way I can adjust the formula better.

 

Regards,

MFelix

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hey MFelix ,

 

Thanks for the reply. I realised the issue now and almost fixed it. Also what are you tryiig to say in the first step? I created a different table with all the four columns in it named Early, Late, Cancelled, On Time but I cannot still add the slicer which gives me all column names in the selection. Can you explain the first step a little more? 

 

It's giving me this error: 

Failed to resolve name 'Slicer'. It is not a valid table, variable, or function name.

 

Thanks!

Niket

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    Hi Anonymous,

     

    In the previous post the name of the table was Slicer that's why it's in the formula.

     

    You need to place the name of the table you used to make the slicer selection.

     

    Regards,

    MFelix