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