Help Creating Calculated Column for Total Category Value

[asalazarjr]

Hello, I need help created a static column for Total Category Value.

I only need the total category value for 1 Quarter in my entire data set.(ex: Q2) and mark rest as 0 value.

Quarter(text)	Category(text)	Sub-Category #(Text)	Value(#)	Category Total (#)
Q1	Fruit	123	$   15.00	0
Q1	Fruit	12	$   20.00	0
Q1	Vegetable	156	$   10.00	0
Q1	Vegetable	152	$   20.00	0
Q2	Vegetable	2525	$   30.00	$               80.00
Q2	Vegetable	2512	$   50.00	$               80.00
Q2	Fruit	6433	$   10.00	$               10.00
Q3	Vegetable	347	$   20.00	0
Q3	Vegetable	3636	$   15.00	0
Q3	Vegetable	98	$   20.00	0
Q3	Fruit	6596	$   30.00	0
Q3	Fruit	272	$   40.00	0
Q4	Vegetable	13	$   50.00	0
Q4	Fruit	167	$   30.00	0
Q4	Vegetable	156	$   10.00	0

Thanks for the suggestions!

[Icey]

Hi @asalazarjr ,

Please let us know if the replies above are helpful.

If they are, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please give us more details.

Best Regards,

Icey

[amitchandak · Accepted solution]

@asalazarjr , Try a new column like

if([Quarter] ="Q2", sumx(filter(table,[Quarter] = earlier([Quarter]) && [Category] = earlier([Category])),[Value]))

[mahoneypat]

You can use this column expression to do that

Total Category =
IF (
    Table[Quarter(text)] = "Q2",
    CALCULATE (
        SUM ( Table[Value(#)] ),
        ALLEXCEPT (
            Table,
            Table[Quarter(text)],
            Table[Category(text)]
        )
    ),
    0
)

Regards,

Pat