Hi All,
Please refer to the attached flow where the subtotals and grandtotals are calculated for every 'Dept'.
Now I need to enhance the flow to accomodate filtering based on the column 'Sub-Dept'.
Eventually all columns in the dataset should be filterable and grandtotal should always be shown based on the selection. The subtotals work as expected.
Do let me know on any questions.
Actually grand totals are in the Null 'Sub-Dept'
Thanks
Hey @JDong
There is already a tool that does sub totals and grand totals. It's part of the CrEW Macros
This is excellent...but in actual data I am working on the fields are strings 🙂
Anyway I get over this ? The reason they are strings are since the amount fields I appended with $.
Thanks
Unfortunately, it is not possible to have the numbers as a string and perform numeric functions to them. The values would need the "$" removed and the value converted to a number prior to any numeric function.
I suggest removing the "$" from your values, performing the calculations, and then adding them back at the end if you really need them. You can bulk remove and bulk add by using the multi-field formula and a replace function or a concatenate:
replace([_CurrentField_],"$","")
"$" + tostring([_CurrentField_])
Hope this helps!
Thanks if you can please read the question, I am already getting the grand total in the flow, problem is if I need to filter on the "Sub-Dept" it wont work.
Is there a way I can create a grandtotal where based on the lines for the Sub-Dept combinations ? So I can filter the data.
So according to me there will be 2 grand total combination lines ?
Thanks
Can you please include a sample dataset of what you have, and what you're looking for? If you could include your workflow, that would be helpful as well.
@echuong1 , @joshuaburkhow @jferrone
Sure.
Dept | Sub Dept | Sales | Cost | Mfg | Profit | Total |
MKT | Afr | 200 | 100 | 15 | 75 | 390 |
MKT | Bar | 100 | 10 | 10 | 80 | 200 |
MKT Total | 300 | 110 | 25 | 155 | 590 | |
Prod | Afr | 10 | 2 | 2 | 6 | 20 |
Prod | Bar | 50 | 2 | 2 | 44 | 98 |
Prod Total | 60 | 4 | 4 | 50 | 118 | |
Grand Total | 360 | 114 | 29 | 205 | 708 |
This is exacty the expected outcome. As in the attached flow. But now if the user filters on "Sub-Dept" he still wants to see the Grand Total row displayed and calculated for the Sub-Dept level.
Anyway to achieve this easily based on the outcome ? Thanks
Updated the output above
Any update here please
@echuong1 @jferrone @joshuaburkhow
As we notice I add a filter on Sub-Dept the Grand total line is not seen. Any way to show the total for this ?
So output should be
Dept | Sub Dept | Sales | Cost | Mfg | Profit |
MKT | MKT_CAN | 200 | 100 | 15 | 75 |
MKT | MKT _ US | 100 | 10 | 10 | 80 |
Grand Total | 300 | 110 | 25 | 155 |