topic Re: Calculate sum of distinct category plus value of each blank category in DAX Commands and Tips

Calculate sum of distinct category plus value of each blank category

qianqianat007 — Fri, 18 Aug 2023 21:04:53 GMT

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

Re: Calculate sum of distinct category plus value of each blank category

qianqianat007 — Fri, 18 Aug 2023 21:11:50 GMT

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

Re: Calculate sum of distinct category plus value of each blank category

Greg_Deckler — Fri, 18 Aug 2023 22:48:20 GMT

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

Re: Calculate sum of distinct category plus value of each blank category

qianqianat007 — Sat, 19 Aug 2023 08:47:12 GMT

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

Re: Calculate sum of distinct category plus value of each blank category

Greg_Deckler — Sat, 19 Aug 2023 12:36:16 GMT

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?

Re: Calculate sum of distinct category plus value of each blank category

tamerj1 — Sat, 19 Aug 2023 18:34:09 GMT

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

Re: Calculate sum of distinct category plus value of each blank category

qianqianat007 — Sun, 20 Aug 2023 09:08:15 GMT

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

Re: Calculate sum of distinct category plus value of each blank category

qianqianat007 — Sun, 20 Aug 2023 09:10:26 GMT

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

Re: Calculate sum of distinct category plus value of each blank category

qianqianat007 — Sun, 20 Aug 2023 09:16:10 GMT

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

Re: Calculate sum of distinct category plus value of each blank category

tamerj1 — Sun, 20 Aug 2023 10:48:27 GMT

qianqianat007

Can you please paste the code that you have tried?

Re: Calculate sum of distinct category plus value of each blank category

tamerj1 — Sun, 20 Aug 2023 10:48:29 GMT

qianqianat007

Can you please paste the code that you have tried?

Re: Calculate sum of distinct category plus value of each blank category

qianqianat007 — Sun, 20 Aug 2023 10:54:30 GMT

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

Re: Calculate sum of distinct category plus value of each blank category

tamerj1 — Sun, 20 Aug 2023 11:02:50 GMT

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

Re: Calculate sum of distinct category plus value of each blank category

qianqianat007 — Mon, 21 Aug 2023 16:22:24 GMT

wow it works! thanks tamerj1