[EASY QUESTION] Many slicers, few measures.

[brunomoriya]

Hi,

Im new to PBI, also i don't speak english very well, so i'm sorry for my bad english.

The following situation is: I have a list of products, with amount (kg), unit price ($/Kg), and totals. Also, i have a list of % increase/decrease of unit price and amount.

I wish i could evaluate by a "what-if analysis" new scenarios, switching between %'s of increase/decrease of price/quantity, per product and compare with original scenario (multiple selection should also work!). For that, i need many slicers like:

- "Choose the product to increase quantity"

- "Choose % increase (for quantity ( 1% to 10%))

- "Choose the product to increase price"

- "Choose % increase (for price (1% to 10%))

- "Choose the product to decrease quantity"

- "Choose % decrease (for quantity (1% to 10%))"

- And so on....

Example: Compare original scenario, (total 3115,00) vs. reduction of 10% amount of bread and increase of 3% on water price (3153,00)

Product	Amount (kg)	Unit Price ($/Kg)	Totals
Bread	100	1,00	100,00
Rice	200	2,30	460,00
Orange Juice	50	2,00	100,00
Potato	50	1,10	55,00
Water	800	2,00	1.600,00
Meat	200	4,00	800,00
% Price Increase	% Price Decrease	% Amount Increase	% Amount Decrease
0%	0%	0%	0%
1%	1%	1%	1%
2%	2%	2%	2%
3%	3%	3%	3%
4%	4%	4%	4%
5%	5%	5%	5%
6%	6%	6%	6%
7%	7%	7%	7%
8%	8%	8%	8%
9%	9%	9%	9%
10%	10%	10%	10%

Again, sorry for bad english and many thanks!!

[MFelix]

HI @brunomoriya,

I have been working on a similar set-up and this post help me sort out some issues since I like to have challenge more and more in PBI.

I will try to make my explanation as detailed as possible but if you have some questions please tell me:

1. Set-up tables:
   • The slicers table must be made individually and without being in the same table this to allow for you to have the selections made without filtering values from other slicers / tables.
   • Tables created:
   • Products (Your Master data table)
   • Decrease_Products (1 Column Decrease_Product)
   • Increase_Products (1 Column Increase_Product)

     The two table will have the same formula as a base
     
     = DISTINCT(Products[Product])

   • Amount_Decrease (1 Column %_Amount_Decrease)
   • Amount_Increase (1 Column %_Amount_Increase)
   • Price_Decrease (1 Column %_Price_Decrease)
   • Price_Increase (1 Column %_Price_Decrease)
     • Insert all this tables in the Enter Data with values 0% till 10%
     • Keep all the table of Increase / Decrease as a single table do no make any relationships with other tables.
2. Create the following measure on the tables below:
   • Decrease_Products,
   • Increase_Products,

   • Decrease_Validation = SEARCH ( MAXX ( Products, Products[Product] ), [Prod_List_Decrease], 1, 0 )
     
     Formula to find if product is in the decrease list
     
     Decrease_Prod_List =
     CONCATENATEX (
         VALUES ( Decrease_Products[Decrease_Product] ),
         Decrease_Products[Decrease_Product]
     )
     
     List of values to decrease
     
     Repeat this formulas for the Increase making the necessary changes

   • Products

     Decrease =
     SWITCH (
         TRUE (),
         [Decrease_Validation] >  0,
         SUMX (
             Products,
             Products[Amount (kg)]
                 * ( 1 - MAX ( 'Amount Decrease'[% _Amount_Decrease] ) )
                 * Products[Unit Price ($/Kg)]
                 * ( 1 - MAX ( 'Price Decrease'[%_Price_Decrease] ) )
         ), 0
     )
     
     

     Increase = 
     SWITCH (
         TRUE (),
         [Increase_Validation] > 0, 
     		SUMX (
             Products,
             Products[Amount (kg)]
                 * ( 1 + MAX ( 'Amount Increase'[%_ Amount_Increase] ) )
                 * Products[Unit Price ($/Kg)]
                 * ( 1 + MAX ( 'Price Increase'[%_Price_Increase] ) )
         ),
         0
     )
     
     

     Estimated_Value = 
     SUMX (
         Products,
         [Increase] + [Decrease]
             + IF (
                 [Increase] + [Decrease]
                     = 0,
                 Products[Amount (kg)] * Products[Unit Price ($/Kg)],
                 0
             )
     )

     Total Value = SUMX(Products, Products[Amount (kg)]*Products[Unit Price ($/Kg)])

     Variation to Total = [Estimated_Value]-[Total Value]

3. Setting up all the report as you need:

• Add all the Increase / Decrease tables as slicers and then set up the table/ cards with all your set-ups
  • Be aware that I didn't make any validation on the filter in order to know if one is Increasing and Decreasing but that can also be set-up, and also simplified this by making only one table for increase and decrease but you can replicate the table of increase and decrease for Price and Quantity and then adapt the Total Value Formula

Please also download a version of the PBIX file here please be aware it a we transfer link and will only be available for 7 days.

Any question please tell me.

Regards,

MFelix

[brunomoriya]

@MFelix

I've started your explanation but i missed to say an important situation.

Products will not always increase both price and amount. I wish i could choose procuts that increase amount and products that increases prices.. for example:

- Orange juice will increase 1% price and keep amount.

- Potato will increase 3% amount and keep price

- Rice will decrease 7% price and keep amount.

- Water will decrease 2% amount and keep price.

If i want both price and amount increase i can choose "orange juice" and "orange juice".

I will keep trying, but that explanation gave me some ideas.

[MFelix]

Hi @brunomoriya,

There is one question regarding your setup since you want to have different percentages of increase / decrease you will need to have one decrease/increase table per item and per price/quantity this would multiply your options by the amount of value you have.

Don't know if this is based on the option of the user or a preset values that you may have but thinking a lot outside the box you can do this:

1.  Set up parameter
   • Name: Increase_Decrease_Parameter
   • Type: Text
   • Required:True
   • 
   • In this field you need to have the users enter a specific type information I have setup a Text with the following format_
     • Rice / P=-1% / Q = 2% # Bread / P= 0% / Q = -3%
       • / to divide the column
       • # to act as a row breaker
       • P= returns price impact
       • Q= return quantity impact
2. Now set up a new table in the querie editor with the following M code:

   1. let
          Source = Increase_Decrease_Parameter,
          Table_Format = #table(1, {{Source}}),
          Split_Rows = Table.ExpandListColumn(Table.TransformColumns(Table_Format, {{"Column1", Splitter.SplitTextByDelimiter("# ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
          Split_columns = Table.SplitColumn(Split_Rows, "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
          Split_Price = Table.SplitColumn(Split_columns, "Column1.2", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
          Split_Quantity = Table.SplitColumn(Split_Price, "Column1.3", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.3.1", "Column1.3.2"}),
          Format = Table.TransformColumnTypes(Split_Quantity,{{"Column1.3.2", Percentage.Type}, {"Column1.2.2", Percentage.Type}}),
          Pivot_Price = Table.Pivot(Format, List.Distinct(Format[Column1.2.1]), "Column1.2.1", "Column1.2.2", List.Sum),
          Pivote_Quantity = Table.Pivot(Pivot_Price, List.Distinct(Pivot_Price[Column1.3.1]), "Column1.3.1", "Column1.3.2", List.Sum),
          Rename = Table.RenameColumns(Pivote_Quantity,{{"Column1.1", "Product"}})
      in
          Rename

   2. Now use this table to make your measures in your simulation:
   3. Create the following Measures:

      1. Price =
         IF (
             ISBLANK (
                 LOOKUPVALUE (
                     Parameter_Treatment[ P],
                     Parameter_Treatment[Product], MAX ( Products[Product] )
                 )
             ),
             0,
             LOOKUPVALUE (
                 Parameter_Treatment[ P],
                 Parameter_Treatment[Product], MAX ( Products[Product] )
             )
         )
         
         
         Quantity =
         IF (
             ISBLANK (
                 LOOKUPVALUE (
                     Parameter_Treatment[ Q ],
                     Parameter_Treatment[Product], MAX ( Products[Product] )
                 )
             ),
             0,
             LOOKUPVALUE (
                 Parameter_Treatment[ Q ],
                 Parameter_Treatment[Product], MAX ( Products[Product] )
             )
         )

Now just use the measures in your graphs and chart. 

Important notes:

• Always add you parameter as example: Rice / P=-1% / Q = 2% # Bread / P= 0% / Q = -3%
• After changing the parameter update the model.
• 
• This parameter edition option is only available in PBI desktop not online.

Attach PBI file.

Again just thinkg outside the box.

Regards,

MFelix

[brunomoriya]

Hi Felix,

Thats a big explanation! I will follow your instructions and try! Maybe it will take a few days for an answer, thanks!!