topic Re: Sum of column A' values based on distinct values in column B in DAX Commands and Tips

Sum of column A' values based on distinct values in column B

2023Lee — Wed, 06 Dec 2023 13:49:51 GMT

Dear

I got a problem when calculate the distinct sum value.

I want to get the sum value of different "category" based on the distinct value in column "Time", which means I want the value"sum technical =60+15","sum External = 20+10".

I have try this DAX

Technical-Distinct =

calculate(

sum('Test'[Value]),

'Test'[Category]="Technical",

distinct('Test'[Time])

)

But it seems didn't work.😭

Anybody could help that?

Very appreciate it!

ID	Category	Value	Time
1	Technical	60	8/25/2023 11:00
2	Technical	60	8/25/2023 11:00
3	Technical	60	8/25/2023 11:00
1	External	20	8/25/2023 11:00
2	External	20	8/25/2023 11:00
3	External	20	8/25/2023 11:00
4	Technical	15	9/15/2023 17:00
5	Technical	15	9/15/2023 17:00
4	External	10	9/15/2023 17:00
5	External	10	9/15/2023 17:00

Re: Sum of column A' values based on distinct values in column B

BeaBF — Wed, 06 Dec 2023 13:48:00 GMT

2023Lee Hi! Try with:
DistinctSum =
SUMX (
VALUES ( 'Test'[Time] ),
CALCULATE (
SUM ( 'Test'[Value] ),
ALLEXCEPT ( 'Test', 'Test'[Category] )
)
)

BBF

Re: Sum of column A' values based on distinct values in column B

FreemanZ — Wed, 06 Dec 2023 14:31:38 GMT

Hi 2023Lee ，

try like:

Technical-Distinct = SUMX( FILTER( SUMMARIZE( test, test[category], test[time], test[value] ), test[category]="Technical" ), test[value] )

it worked like:

Re: Sum of column A' values based on distinct values in column B

Fowmy — Wed, 06 Dec 2023 14:39:35 GMT

2023Lee

Add a table visual on your canves, add the category and the following measure:

M1 = SUMX( SUMMARIZE( Table10 , Table10[Category] , Table10[Time] ) , CALCULATE( MAX( Table10[Value] ) ) )

Re: Sum of column A' values based on distinct values in column B

2023Lee — Thu, 07 Dec 2023 07:09:56 GMT

So nice of you!

Very thanks you give the solution of this case, that is what I want.

I also can apply this to many similar cases.🤗

Re: Sum of column A' values based on distinct values in column B

2023Lee — Thu, 07 Dec 2023 07:39:24 GMT

Hi Fowmy

I tried your solutions, it looks very well when I use use a table or chart visual, just need to add the category as column/legend.

Tnanks for your sharing, which also gives me some ideas to get more data.😀