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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi all,

 

I have a somehow similar issue. I need a filter or slicer where I can choose the column (single or multiple) names which data should be shown in the diagram.

 

Following data table:

CWMercedesBMWAudiVW
186102108110
2968986100
392839884
481747693

(CW=calender week)

 

I want to create a line chart, where the CW is in the X-axis and in Y-axis I have the values from Mercedes, Audi etc..(see below)

In Slicer/Filter I want to be able to select one or multiple of the categories (column names), which will be shown then in the line chart or table visualisation.

 

 

It would be great if somebody could give me an easy solution, because I have 53 different columns and 42 calender weeks in my table  🙂

 

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

    HI Anonymous ,

     

    Based on the image you are showing the best option is to unpivot you data to get a single column for values and a single colunm for the brands something similar to this:

     

     

    Full code for Power Query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYyxDcAwCAR3+dqFia0YZkHsv0ZypEjzOnH/ZMo05PcbNq9OJ22qRopLID3+2qcWCu9NrHy32SBvD3QYxVLVAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CW = _t, Mercedes = _t, BMW = _t, Audi = _t, VW = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CW", Int64.Type}, {"Mercedes", Int64.Type}, {"BMW", Int64.Type}, {"Audi", Int64.Type}, {"VW", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"CW"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

    The you can use values on Y Axis and the Attribute on the legend.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks, that was exactly the tipp I needed 👍