Need help with a measure, tried but failed

[rp2022]

Input: 

Category1	Category2	Count
A	1	100
A	2	50
B	1	200
B	2	400
B	3	100
B	4	300
C	1	300
C	2	200
C	3	100

Expected Output:

Category1	Category2	Count	SUM(Category2 =2)	Comp
A	1	100	50	High
A	2	50	50	Equal
B	1	200	400	Low
B	2	400	400	Equal
B	3	100	400	Low
B	4	300	400	Low
C	1	300	200	High
C	2	200	200	Equal
C	3	100	200	Low

Tried: 

Var no =

            CALCULATE(

                SUM(Table[Count]),

                ALL(Table[Category1]),

                Table[Category2] = "2"

        )

But the results are wrong

[lbendlin]

Sum2 = CALCULATE(sum('Table'[Count]),'Table'[Category2]=2)

Comp =

var s = sum('Table'[Count])

return switch(TRUE(),

s>[Sum2],"High",

s=[Sum2],"Equal",

"Low")

[Wilson_]

Your Sum2 is what I tried first but for some reason, it didn't work for me. 😅 Your Comp measure is definitely more succinct though! 😄

You also just have the one table of data right?

[lbendlin]

You probably tried it as a measure.

[rp2022]

Of course, they worked for me when they are both measures

[Wilson_ · Accepted solution]

Hello rp2022,

Try this for your Category 2 is 2 measure:

Category 2 is 2 Total = 
VAR Cat1 = SELECTEDVALUE ( 'Table'[Category1] )
VAR Filtered =
FILTER (
    ALL ( 'Table' ),
    'Table'[Category2] = 2 &&
    'Table'[Category1] = Cat1
)
VAR Result =
SUMX (
    Filtered,
    'Table'[Count]
)

RETURN Result

and try this for your Comp measure:

Comp = 
VAR SumCount = SUM ( 'Table'[Count] )
VAR CompValue = [Category 2 is 2 Total]
VAR CompVar = SumCount - CompValue
VAR Result =
SWITCH (
    TRUE(),
    CompVar > 0, "High",
    CompVar = 0, "Equal",
    CompVar <= 0, "Low"
)

RETURN IF ( ISINSCOPE ( 'Table'[Category1] ), Result )

This is what I get when I tested it with your sample data:

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[rp2022]

Thank you so much, it worked like a charm