Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Create Slicer based on four fields.

I am new to BI and was hoping someone could provide a solution to the following:

 

I have a dataset that includes thousands and thousands of rows of order level detail. Within this data I have information such as Products, Location, COGS, etc. Outside of this, I have four different pricing schedules (ABCD) that I have been able to add into this dataset as new columns based on Product and Location relationship. I am attempting to analyze the profitability of each schedule. Ideally I'd like to create a slicer that allows me to select A, B, C, D and my visuals adjust accordingly. With the way I have the table set up now, I would have to create these visuals on four different pages.

 

Any help would be much appreciated.

 

Thanks,

 

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    Anonymous

     

    According to your description, I assume you have a table as below.

     

     

    You can create a new table which includes a column for pricing schedules selection. Then you can get the expected result by create one measure. Please refer to following steps.

     

    1. Create a new table for pricing schedules selection

    2. Create a measure to calculate the profitability according to the slicer selection.
      Profitability = 
IF (
    HASONEVALUE ( Schedules[Schedules] ),
    SWITCH (
        VALUES ( Schedules[Schedules] ),
        "A", SUM ( Price[Pricing Schedules A] ) - SUM ( Price[COGS] ),
        "B", SUM ( Price[Pricing Schedules B] ) - SUM ( Price[COGS] ),
        "C", SUM ( Price[Pricing Schedules C] ) - SUM ( Price[COGS] ),
        "D", SUM ( Price[Pricing Schedules D] ) - SUM ( Price[COGS] )
    ),
    BLANK ()
)