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

[2023Lee]

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

[FreemanZ · Accepted solution]

Hi @2023Lee ，

try like:

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

it worked like:

[Fowmy · Accepted solution]

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

[Fowmy · Accepted solution]

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

[2023Lee]

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.😀

[FreemanZ · Accepted solution]

Hi @2023Lee ，

try like:

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

it worked like:

[2023Lee · Accepted solution]

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.🤗

[BeaBF]

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

BBF