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
5 years ago

MFelix 

I have the following Master table where "Monday7","Tuesday7","Monday30","Tuesday30" represent the Utilization percentage of corresponding product name. For example: "Monday30" represent the utilization percentage of "Water Purifier" of all Mondays in past 30 days.

 

Similarly there are 4 categories (7 days, 30 days, 60 days, 90 days) where for each category, a separate columns will be present for all days of the week.

 

Name               Monday7      Tuesday7       Monday30    Tuesday30
Water Purifier       43                 44                   64                  32
Refrigerator          54                 12                   32                  45


Now i need to create two slicers, one to select the category ( "7 days","30 days", "60 days", "90 days") and second one to select the day of the week ("Mon","Tue","Wed","Thur","Fri","Sat", "Sund")

 

Now once users selects an option from both the slicers, I wanted to show all the visualizations in that page with the following data where the new column Utilization percent should show the data same as "Monday7" column if user selects "7 days" and "Monday" in slicer.

 

Name              UtilizationPercent
Water Purifier            43
Refrigerator               54


Is this possible? can you please guide me?