Create Slicer based on four fields.

[Anonymous]

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,

[v-sihou-msft]

@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 ()
   )
   

   
   

[kcantor]

Create a lookup table using A,B,C, and D. Relate this to your fact table. Use this table as your slicer and it should filter all results.

This link will help with lookup table creation.

http://www.powerpivotpro.com/2014/03/creating-dynamic-lookup-tables-with-unique-values-using-power-q...