[EASY QUESTION] Many slicers, few measures.

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