Calculate sum of distinct category plus value of each blank category

[qianqianat007]

Dear community, I am looking for a way to calculate sum of a column ("Value" colume in the table shown below). Appreciate any help you can provide. Thanks.

	Category 1	Value
		50
		40
	A	30
	A	30
	B	20
	C	10
	C	10
Expected result	50+40+30+20+10	150

[tamerj1]

Hi @qianqianat007 

please try

Sum =
SUMX (
VALUES ( 'Table'[Category] ),
IF (
'Table'[Category] = BLANK (),
SUMX ( CALCULATETABLE ( 'Table' ), 'Table'[Value] ),
SUMX ( CALCULATETABLE ( VALUES ( 'Table'[Value] ) ), 'Table'[Value] )
)
)

[qianqianat007]

Dear @tamerj1 thanks for sharing your idea! I tried it and get 240 as result (expect 200).

[tamerj1]

@qianqianat007 

Can you please paste the code that you have tried?

[tamerj1]

@qianqianat007 

Can you please paste the code that you have tried?

[qianqianat007]

Dear @tamerj1 I tried your this code and got 240 as result (expect 200).

Sum =
SUMX (
VALUES ( 'Table'[Category] ),
IF (
'Table'[Category] = BLANK (),
SUMX ( CALCULATETABLE ( 'Table' ), 'Table'[Value] ),
SUMX ( CALCULATETABLE ( VALUES ( 'Table'[Value] ) ), 'Table'[Value] )
)
)

[tamerj1 · Accepted solution]

@qianqianat007 
Please refer to attached sample file

Sum = 
SUMX (
    VALUES ( 'Table'[Category] ),
    IF (
        'Table'[Category] = BLANK (),
        SUMX ( CALCULATETABLE ( 'Table' ), 'Table'[Value] ),
        SUMX ( CALCULATETABLE ( VALUES ( 'Table'[Value] ) ), 'Table'[Value] )
    )
)

[qianqianat007]

wow it works! thanks @tamerj1 

[qianqianat007]

Dear community, I am looking for a way to calculate sum of a column ("Value" colume in the table shown below). Appreciate any help you can provide. Thanks.

	Category 1	Value
		50
		40
	A	30
	A	30
	B	20
	C	10
	C	10
Expected result: 50+40+30+20+10=150

[qianqianat007]

@Greg_DecklerIt works very well for this data sample, thank you! But in my real data, empty category rows can sometimes have same value (see below, expected result is 200). Any idea how can I solve this?

Category	Value
	50
	40
	50
A	30
A	30
B	20
C	10
C	10

[Greg_Deckler]

@qianqianat007 I'm not quite clear as to the logic of what is supposed to be summed and what is not supposed to be summed. What is supposed to be summed in this latest example in order to get you to 200?

[qianqianat007]

Dear @Greg_Deckler, in the latest example, for rows where Category is empty (row 1, 2, 3), each Value should be counted (50 + 50 + 40); for rows where Category is not empty (row 5 to 9), only distinct value should be counted (30 + 20 + 10).  Hope that's clear now.. Thanks!

Category	Value
	50
	50
	40
A	30
A	30
B	20
C	10
C	10
expected result: 50 + 50 + 40 + 30 + 20 + 10 = 200

[qianqianat007]

a small correction: row 5 to 9 --> row 4 to 8

[Greg_Deckler]

@qianqianat007 Maybe something like SUMX(DISTINCT('Table'[Value]), [Value]) ?