Multiple Selection of List items and their total

gregoryTan
gregoryTan
in Modeling

Hi, Anaplaners

 

I have faced an issue where I am not able to select multiple list items (in the attached excel, Product A and Prodcut B) to show what is selected and their totals. What I came up with is when products in the row do not equal to Selctor 1 or Selector 2, set 0 and hide 0s. But the problem is this idea would not work if you want to show all products (Resest in a dashboard will not solve this)

Tagged:

Best Answer

  • LipChean_Soh
    LipChean_Soh
    Answer ✓

    Hi Gregory,

     

    Let's refer to the module in your Excel as "P&L", and it has 2 lists, i.e. "Products" and "Time". There are 2 line items "Sales" and "COGS".

     

    You can then create a module called "Product Selection" using only the "Products" list, and a boolean line item called "Select".

     

    A 3rd module called "P&L Report" is created, and it has 2 lists, i.e. Products and Time. There are 2 line items, i.e. Sales and COGS

    Put in the following formula:

    P&L Report.Sales = If Product Selection.Select then P&L.Sales else 0

    P&L Report.COGS = If Product Selection.Select then P&L.COGS else 0

     

    To select all the Products, you'll need to tick all the checkbox in Product Selection. And this can be tedious, so you can also create a 4th module called "All Product" without any list, but create 1 boolean line item called "Select".

     

    So now you can slightly modify the line items in the 3rd module created above, i.e. 

    P&L Report.Sales = If All Product.Select or Product Selection.Select then P&L.Sales else 0

    P&L Report.COGS = If All Product.Select or Product Selection.Select then P&L.COGS else 0

     

    There are of course futher improvements that can be suggested, but why don't you try the above first.

     

    Thanks,

    LipChean

     

Categories