Totalling Different Types by Sub Category in one s...

marsbar · 01-24-2023 05:02 AM

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!!

dbaum

@marsbar wrote:

individual virtual columns with valid if set to the above sub categories

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.

@marsbar wrote:

it wouldn't let me

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

SUM(SliceA1[Total £])

marsbar

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 £])

dbaum

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.

TeeSee1

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?

Totalling Different Types by Sub Category in one slice?