Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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] ) )
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.🤗
@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] ) )
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.😀
Hi @2023Lee ,
try like:
Technical-Distinct =
SUMX(
FILTER(
SUMMARIZE(
test,
test[category],
test[time],
test[value]
),
test[category]="Technical"
),
test[value]
)
it worked like:
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.🤗
@2023Lee Hi! Try with:
DistinctSum =
SUMX (
VALUES ( 'Test'[Time] ),
CALCULATE (
SUM ( 'Test'[Value] ),
ALLEXCEPT ( 'Test', 'Test'[Category] )
)
)
BBF