Totalling Different Types by Sub Category in one slice?

AppSheet AppSheet Q&A
,
1

Hi All - Please help!!

I have a table made up of three TYPE Columns (Type A/Type B/Type C) - there are also columns for costs associated with these (Type A £/Type B £/Type C £). I also have a Total £ column combining the other £ columns.

Within each type there are sub categories - this is where it gets tricky.

I want a total of Type A depending on certain Sub Categories - Straightforward with a slice.

I want a total of Type A depending on certain Sub Categories - Straightforward with a slice.

I want a total of Type A depending on certain Sub Categories - Straightforward with a slice.

I want a total of Type A/Type B/Type C depending on certain Sub Categories - When I try to combine all subcategories/Types there are the following issues:

OR([Type A]=Sub Cat 1, [Type A]=Sub Cat 2, [Type A]=Sub Cat 3, [Type B]=Sub Cat 4, [Type B]=Sub Cat 5, [Type B]=Sub Cat 6, [Type C]=Sub Cat 7, [Type C]=Sub Cat 8, [Type C]=Sub Cat 9)

When this slice is used it adds not only the values I want above but it also includes e.g:

[Type B]=Sub Cat 1 as this happens to be a choice picked in the same row as**[Type A]=Sub Cat 1** etc etc etc.

Is there a formula that allows me to slice to show a total sum of:

Type A £ only if [Type A]=Sub Cat 1, [Type A]=Sub Cat 2, [Type A]=Sub Cat 3

Type B £ only if [Type B]=Sub Cat 4, [Type B]=Sub Cat 5 [Type B]=Sub Cat 6

Type C £ only if**[Type C]=Sub Cat 7, [Type C]=Sub Cat 8 [Type C]=Sub Cat 9**

…all in one slice? Ive tried doing all of these as individual virtual columns with valid if set to the above sub categories, then when I tried to make a virtual column adding the values of these it wouldn’t let me.

I understand this is clear as mud but its hard to explain what I mean!!

2

It’s unclear what this means. The Valid if property is irrelevant in a virtual column. If you didn’t already do so, put your expression instead in the virtual column’s’ App formula property.

What does this mean? You should be able to make a column with an App formula expression like:

SUM(SliceA1[Total £])
2 Likes
3

Hi Sorry- does this mean that I could create a slice for:

only if [Type A]=Sub Cat 1, [Type A]=Sub Cat 2, [Type A]=Sub Cat 3

another slice for only if [Type B]=Sub Cat 4, [Type B]=Sub Cat 5 [Type B]=Sub Cat 6

another slice for [Type C]=Sub Cat 7, [Type C]=Sub Cat 8 [Type C]=Sub Cat 9

then create a virtual column with SUM(SliceA1[Type A £]) + (SliceB1[Type B £]) + (SliceC1[Type C £])

4

I still don’t understand well what functionality you’re specifically asking about–much less what need you’re trying to meet. I suggest share screenshots–maybe of your data source–that clarify what columns are populated for a given row and what data types are in each column. Then, also explain or illustrate what sums you want to calculate.

1 Like
5 
AND(
    IN(
        [Type A],
        LIST(
            "Sub Cat 1",
            "Sub Cat 2",
            "Sub Cat 3"
        )
    ),
    IN(
        [Type B],
        LIST(
            "Sub Cat 4",
            "Sub Cat 5",
            "Sub Cat 6"
        )
    ),
     IN(
        [Type C],
        LIST(
            "Sub Cat 7",
            "Sub Cat 8",
            "Sub Cat 9"
        )
    )
)

Maybe this is what you are looking for?

1 Like