How to calculate based on what has been selected in a multiple dropdown list

[acolyter]

Hi,

I'm trying to build an app in which I want the user to be able to select an item from a dropdown list. In fact, I want him to be able to select multiple items, and I want for each item to have set a value that if he selects it, it adds up to the total sum.

Example: I have 3 choices (Blue - 10$, Red - 5$, Green - 7$). I select Blue and Red. I want to have a text label that says 15$ (10$ (Blue) + 5$ (red)).

Is there a way to do this? I've managed to do something, but it only calculates the last selected item (so if I select Blue AND Red, it only counts with Red and says 5$).

Thanks!!

[StalinPonnusamy · Accepted solution]

Hi @acolyter 

I assume you meant Combo box for multi-selection.

ClearCollect(ColItemsWithPrice,
{ItemName:"Blue",Price:10},
{ItemName:"Red",Price:5},
{ItemName:"Green",Price:7}
)

Set the Items property of Combo box to

Sort(ColItemsWithPrice.ItemName,ItemName)

Set the Text property of Label control to

Sum(
    With(
        {Item: ColItemsWithPrice},
        Filter(
            Item,
            ItemName in (ItemsComboBox.SelectedItems.ItemName)
        )
    ),
    Price
)

Thanks,
Stalin - Learn To Illuminate

[KatLane]

Hi @StalinPonnusamy 

I want to do the same thing, but using check boxes, so that I can find the sum of the values of the checked items.  Multiple items will need to be able to be selected / checked.

Can you please help?

Thank you.

[StalinPonnusamy]

Hi @KatLane 

Assume you have a collection/data similar to

ClearCollect(ColItemsWithPrice,
{ItemName:"Blue",Price:10},
{ItemName:"Red",Price:5},
{ItemName:"Green",Price:7},
{ItemName:"Yellow",Price:3},
{ItemName:"Orange",Price:12},
{ItemName:"Black",Price:15}
)

Set the Items property of the gallery (ColorWithPriceGallery) to

ColItemsWithPrice

Set the Text property of the Label control to

Sum(
    With(
        {ColorItems: ColorWithPriceGallery.AllItems},
        Filter(
            ColorItems,
            chkColor.Value
        )
    ),
    Price
)

Sample for Reference

Note: ColorItems is a local variable used within With operation

Thanks, Stalin (Microsoft MVP)
Blog - Learn To Illuminate Blog
YouTube - Learn To Illuminate Videos

[StalinPonnusamy]

Hi @KatLane 

We can do sum in another way like

Sum(
    Filter(
        ColorWithPriceGallery.AllItems,
        chkColor.Value
    ),
    Price
)

Similar

Thanks, Stalin (Microsoft MVP)
Blog - Learn To Illuminate Blog
YouTube - Learn To Illuminate Videos

[KatLane]

Thank you @StalinPonnusamy , will definitely try that.  However, my items field is pulling Choices from a SP list.  Do I need to break it down within the app to be able to collect them?  I have the price within the text of the item, but it's not separated out with the price as above so I'm not sure how it would find the value of each item when pulling from the list.

Thank you!

[StalinPonnusamy]

Hi @KatLane

I hope you are looking similar to this.

Set the text property of the label control to get Sum

Sum(
    Filter(
        PhotographyGallery.AllItems,
        chkPhotography.Value
    ),
    Last(
        Split(
            PhotographyTitle.Text,
            "$"
        )
    ).Result
)

PhotographyGallery - GalleryName

chkPhotography - Checkbox Name

PhotographyTitle - Label control where you display the choice names

Reference

Thanks, Stalin (Microsoft MVP)
Blog - Learn To Illuminate Blog
YouTube - Learn To Illuminate Videos

[StalinPonnusamy]

Hi @KatLane 

No need to separate the price. We can use the Split function to get the price of the item.

Thanks, Stalin (Microsoft MVP)
Blog - Learn To Illuminate Blog
YouTube - Learn To Illuminate Videos

[KatLane]

Oh I'm so close @StalinPonnusamy !

The formula is throwing an error, I think because of the PhotographyTitle label control.  I don't have one.

I can see in your screenshot you've got the gallery, and within that the checkboxes, a separator and the label.  But I don't have that on my end.  Mine is just the gallery and checkboxes.  And below that, the form heading.

I replaced that section of the formula with the name of the checkbox and that removed the error, but didn't give me a result when I tested it.  It's still an empty text box.

Also, when I do get it to work, can I display the amount as currency?

Thanks

Kat

[StalinPonnusamy]

Hi @KatLane 

PhotographyTitle is a label control that displays the item name from the Choices list. I have only one control which is choices like yours.

To display as currency,

Text(Sum(
    Filter(
        PhotographyGallery.AllItems,
        chkPhotography.Value
    ),
    Last(
        Split(
            PhotographyTitle.Text,
            "$"
        )
    ).Result
),"[$-en-US]$#,###.00")

Reference

Thanks, Stalin (Microsoft MVP)
Blog - Learn To Illuminate Blog
YouTube - Learn To Illuminate Videos