Solved: Auto Fill sum value based on 2 parameters

DPM_reports · 03-09-2020 03:17 AM

hi,

i want a certain to auto populate itself with the total quantity based on previous cell selections i make that is lot no. and brand…here in the screenshot you can see that in pic 1 i have the data of a particular lot along with lot no., brand name, qty…

as in pic 2 when i select brand and lot no. i want the total of a prticular brand in a particular lot no. to be summed up and fill automatically…

as in the screenshot when i select lot no. 4 and brand as pink…i must get the value in qty as 103 (100+3).

what expression can help me in getting this done…!

PIC 1

PIC 2

Koichi_Tsuji

There will be bunch of workaround to achieve the same goal and task.

For instance, you will use PIC 2 table as sort of “calculator” to dynamically calculate the total, based on the user selections. To do that, follow those steps.

PIC 2 table, delete Date field as it believes it will not be need, unless you have reason to keep that field.
Instead, name that field as [ID] On the spread sheet, add the value to this ID fields randum text.
Read PIC2 table and set “update” only.
for [Brand] Field, add formula Unique(Select(PIC1[Brand], true) ) as suggested value. This will dynamically generate the dropdown based on the value of PIC1 [Brand] without creating master table.
Do the same for [Lot No.] field, i.e. place the formula to suggested value with formula Unique(Select(PIC1[Lot No.], true) )
For PIC2 [Qty] field, add App formula
sum(Select(PIC1[Qty],and([_thisrow].[Brand]=[Brand],[_thisrow].[Lot No.]=[Lot No.])))
Create Detail View out of PIC2 table. Set Brand and Lot No. fields as QUICK EDIT.

Your user visit the detail view you created on step 7, and they select out of dropdown, then dynamically calculate value. but this PIC2 should be shared across the users. So if you want to keep this table assigned to each users, then turn on private mode on that table.

View solution in original post

Koichi_Tsuji

There will be bunch of workaround to achieve the same goal and task.

For instance, you will use PIC 2 table as sort of “calculator” to dynamically calculate the total, based on the user selections. To do that, follow those steps.

PIC 2 table, delete Date field as it believes it will not be need, unless you have reason to keep that field.
Instead, name that field as [ID] On the spread sheet, add the value to this ID fields randum text.
Read PIC2 table and set “update” only.
for [Brand] Field, add formula Unique(Select(PIC1[Brand], true) ) as suggested value. This will dynamically generate the dropdown based on the value of PIC1 [Brand] without creating master table.
Do the same for [Lot No.] field, i.e. place the formula to suggested value with formula Unique(Select(PIC1[Lot No.], true) )
For PIC2 [Qty] field, add App formula
sum(Select(PIC1[Qty],and([_thisrow].[Brand]=[Brand],[_thisrow].[Lot No.]=[Lot No.])))
Create Detail View out of PIC2 table. Set Brand and Lot No. fields as QUICK EDIT.

Your user visit the detail view you created on step 7, and they select out of dropdown, then dynamically calculate value. but this PIC2 should be shared across the users. So if you want to keep this table assigned to each users, then turn on private mode on that table.

DPM_reports

i had set column type of qty to number …i got error.

when i set to list type i m getting error as in pic below.

is there a work around for this

Koichi_Tsuji

It must be number type instead of list.

DPM_reports

i am still getting error when type set to number as in screenshot below

Koichi_Tsuji

You need to warp expression by sum

DPM_reports

could you pls elobarate and help by writing the expression in full

Koichi_Tsuji

It is in there in my previous post.

DPM_reports

Thanks…for the quick response…your solution worked…

Koichi_Tsuji

Great.