Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I want to create a measure to calculate sum of roles based on distinct IDs and Latest = "Latest"
I tried the below expression but not getting the desired output
Total = CALCULATE(SUM('table1'[Roles]),'table1'[Latest]="Latest",DISTINCT('table1'[Id])
Roles | ID | Latest |
18 | 010506e1-e5cc-41b6-96c1-ea3a7cb9942c | Latest |
18 | 010506e1-e5cc-41b6-96c1-ea3a7cb9942c | Old |
7 | 01ff947c-b0ff-43f8-bb62-658f23a54db8 | Latest |
7 | 01ff947c-b0ff-43f8-bb62-658f23a54db8 | Old |
10 | 0283453b-b6e8-4bc2-ae2d-1ed5f7b9f57a | Latest |
10 | 0283453b-b6e8-4bc2-ae2d-1ed5f7b9f57a | Old |
13 | 02c39a7d-a369-41b4-9128-7c5103127bfc | Latest |
13 | 02c39a7d-a369-41b4-9128-7c5103127bfc | Old |
9 | 0319f4f1-d21d-4d7f-b573-3f43248a5c21 | Latest |
9 | 0319f4f1-d21d-4d7f-b573-3f43248a5c21 | Old |
15 | 0321fa1d-b136-4b5d-8399-e3c6cde868d6 | Latest |
15 | 0321fa1d-b136-4b5d-8399-e3c6cde868d6 | Old |
10 | 034757c8-7c6a-4fd9-b5b9-e924f5ca76bc | Latest |
10 | 034757c8-7c6a-4fd9-b5b9-e924f5ca76bc | Old |
7 | 0434bc24-29c0-4963-9e86-1705a24fd268 | Latest |
7 | 0434bc24-29c0-4963-9e86-1705a24fd268 | Old |
10 | 04544604-5965-4e60-ac17-4454492bbeae | Latest |
10 | 04544604-5965-4e60-ac17-4454492bbeae | Old |
72 | 04548ef4-bb68-4bc7-aa12-f9bf17109289 | Old |
73 | 04548ef4-bb68-4bc7-aa12-f9bf17109289 | Latest |
9 | 04f485ff-8cfd-4628-a414-1af6fb2545e4 | Latest |
9 | 04f485ff-8cfd-4628-a414-1af6fb2545e4 | Old |
20 | 0516499f-dffc-4110-a831-a842bdb52690 | Latest |
20 | 0516499f-dffc-4110-a831-a842bdb52690 | Old |
17 | 053bed0a-5cfb-41c6-a736-e16b1495a01a | Latest |
17 | 053bed0a-5cfb-41c6-a736-e16b1495a01a | Old |
11 | 0557dd16-c398-4106-b69d-8a308a7622f0 | Latest |
11 | 0557dd16-c398-4106-b69d-8a308a7622f0 | Old |
1 | 05dd6869-bae0-49c1-8ba3-5a84a7e04118 | Latest |
1 | 05dd6869-bae0-49c1-8ba3-5a84a7e04118 | Old |
10 | 05e8cd49-9fc7-4911-a1e9-a00d1e80151f | Latest |
10 | 05e8cd49-9fc7-4911-a1e9-a00d1e80151f | Old |
12 | 0763976f-4a7b-43da-894d-531c6d09c608 | Latest |
12 | 0763976f-4a7b-43da-894d-531c6d09c608 | Old |
103 | 0837d753-97ac-46c8-b0f3-c5a1a0e3740a | Latest |
Output
Total 355
For each subscription as below value of Role when added in Visual.
Role | ID | Latest |
18 | 010506e1-e5cc-41b6-96c1-ea3a7cb9942c | Latest |
7 | 01ff947c-b0ff-43f8-bb62-658f23a54db8 | Latest |
10 | 0283453b-b6e8-4bc2-ae2d-1ed5f7b9f57a | Latest |
13 | 02c39a7d-a369-41b4-9128-7c5103127bfc | Latest |
9 | 0319f4f1-d21d-4d7f-b573-3f43248a5c21 | Latest |
15 | 0321fa1d-b136-4b5d-8399-e3c6cde868d6 | Latest |
10 | 034757c8-7c6a-4fd9-b5b9-e924f5ca76bc | Latest |
7 | 0434bc24-29c0-4963-9e86-1705a24fd268 | Latest |
10 | 04544604-5965-4e60-ac17-4454492bbeae | Latest |
73 | 04548ef4-bb68-4bc7-aa12-f9bf17109289 | Latest |
9 | 04f485ff-8cfd-4628-a414-1af6fb2545e4 | Latest |
20 | 0516499f-dffc-4110-a831-a842bdb52690 | Latest |
17 | 053bed0a-5cfb-41c6-a736-e16b1495a01a | Latest |
11 | 0557dd16-c398-4106-b69d-8a308a7622f0 | Latest |
1 | 05dd6869-bae0-49c1-8ba3-5a84a7e04118 | Latest |
10 | 05e8cd49-9fc7-4911-a1e9-a00d1e80151f | Latest |
12 | 0763976f-4a7b-43da-894d-531c6d09c608 | Latest |
103 | 0837d753-97ac-46c8-b0f3-c5a1a0e3740a | Latest |
Use below dax for your calculation.
Total=SUMX(DISTINCT( FILTER(table1,table1[Latest]="Latest")),table1[Roles])
@Anonymous
CALCULATE(Sum('table1'[Roles]),'table1'[Latest]="Latest")
Sumx(Sumamrize(Filter('table1','table1'[Latest]="Latest"),'table1'[Roles]),[Roles])
Do you need add roles or count
CALCULATE(count('table1'[Roles]),'table1'[Latest]="Latest")
countX(Sumamrize(Filter('table1','table1'[Latest]="Latest"),'table1'[Roles]),[Roles])
@amitchandak I tried these but it is adding duplicate count as well since the ID can be duplicate ..
I want to claculate Sum of roles = where ID is ditinct and Latest column = Latest.
Sample data in below comments
@Anonymous - I believe what you want is the following:
Measure =
SUMX(
SUMMARIZE(
FILTER('Table','Table'[Latest]="Latest"),
'Table'[ID]),
"__Roles",AVERAGE([Roles])
),
[__Roles]
)
So, filter out only those with "Latest", then summarize by ID to group together duplicates. Average roles together since there are duplicates. Then SUMX the roles across the entire filtered, summarized table.
@Greg_Deckler Getting Syntax error
Wont averaging out cause the issues with calculations as there could be multiple repeats not necessarly 1 duplicate in ID Column
Can t we have expression to filter first that Latest then have distinct IDs and corresponding do Addition of roles .
@Anonymous Syntax, had a stray )
Measure =
SUMX(
SUMMARIZE(
FILTER('Table','Table'[Latest]="Latest"),
'Table'[ID],
"__Roles",AVERAGE([Roles])
),
[__Roles]
)
@Anonymous
Try this measure, you can keep only the IDs in the table visual:
Total Roles = CALCULATE(SUM(Table2[Roles]), Table2[Latest] = "Latest")
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy I also need to add this measure on the card visual ..
Can we add any logic to take sum for distinct ids and latest field .
@Anonymous
As per the sample data you provided, the measure I shared should work ona card visual as well.
You needn't take distinct as you
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy I found the catch on the sample data . There could be duplicate ID with both marked as Latest on the cloumn.
For the duplicate ID, I just want to consider one row for Role Summation.
Sum of Roles where Distinct ID and Latest = Latest
Roles | ID | Latest |
18 | 010506e1-e5cc-41b6-96c1-ea3a7cb9942c | Latest |
18 | 010506e1-e5cc-41b6-96c1-ea3a7cb9942c | Latest |
18 | 010506e1-e5cc-41b6-96c1-ea3a7cb9942c | Old |
7 | 01ff947c-b0ff-43f8-bb62-658f23a54db8 | Latest |
7 | 01ff947c-b0ff-43f8-bb62-658f23a54db8 | Latest |
7 | 01ff947c-b0ff-43f8-bb62-658f23a54db8 | Old |
10 | 0283453b-b6e8-4bc2-ae2d-1ed5f7b9f57a | Latest |
10 | 0283453b-b6e8-4bc2-ae2d-1ed5f7b9f57a | Old |
13 | 02c39a7d-a369-41b4-9128-7c5103127bfc | Latest |
13 | 02c39a7d-a369-41b4-9128-7c5103127bfc | Old |
9 | 0319f4f1-d21d-4d7f-b573-3f43248a5c21 | Latest |
9 | 0319f4f1-d21d-4d7f-b573-3f43248a5c21 | Old |
15 | 0321fa1d-b136-4b5d-8399-e3c6cde868d6 | Latest |
15 | 0321fa1d-b136-4b5d-8399-e3c6cde868d6 | Old |
10 | 034757c8-7c6a-4fd9-b5b9-e924f5ca76bc | Latest |
10 | 034757c8-7c6a-4fd9-b5b9-e924f5ca76bc | Old |
7 | 0434bc24-29c0-4963-9e86-1705a24fd268 | Latest |
7 | 0434bc24-29c0-4963-9e86-1705a24fd268 | Latest |
7 | 0434bc24-29c0-4963-9e86-1705a24fd268 | Old |
10 | 04544604-5965-4e60-ac17-4454492bbeae | Latest |
10 | 04544604-5965-4e60-ac17-4454492bbeae | Old |
72 | 04548ef4-bb68-4bc7-aa12-f9bf17109289 | Old |
73 | 04548ef4-bb68-4bc7-aa12-f9bf17109289 | Latest |
9 | 04f485ff-8cfd-4628-a414-1af6fb2545e4 | Latest |
9 | 04f485ff-8cfd-4628-a414-1af6fb2545e4 | Old |
20 | 0516499f-dffc-4110-a831-a842bdb52690 | Latest |
20 | 0516499f-dffc-4110-a831-a842bdb52690 | Old |
17 | 053bed0a-5cfb-41c6-a736-e16b1495a01a | Latest |
17 | 053bed0a-5cfb-41c6-a736-e16b1495a01a | Latest |
17 | 053bed0a-5cfb-41c6-a736-e16b1495a01a | Old |
11 | 0557dd16-c398-4106-b69d-8a308a7622f0 | Latest |
11 | 0557dd16-c398-4106-b69d-8a308a7622f0 | Old |
1 | 05dd6869-bae0-49c1-8ba3-5a84a7e04118 | Latest |
1 | 05dd6869-bae0-49c1-8ba3-5a84a7e04118 | Latest |
1 | 05dd6869-bae0-49c1-8ba3-5a84a7e04118 | Old |
10 | 05e8cd49-9fc7-4911-a1e9-a00d1e80151f | Latest |
10 | 05e8cd49-9fc7-4911-a1e9-a00d1e80151f | Old |
12 | 0763976f-4a7b-43da-894d-531c6d09c608 | Latest |
12 | 0763976f-4a7b-43da-894d-531c6d09c608 | Old |
103 | 0837d753-97ac-46c8-b0f3-c5a1a0e3740a | Latest |
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |