[EASY QUESTION] Many slicers, few measures.

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