Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure to calculate sum based on distinct value from another column

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

 
 
RolesIDLatest
18010506e1-e5cc-41b6-96c1-ea3a7cb9942cLatest
18010506e1-e5cc-41b6-96c1-ea3a7cb9942cOld
701ff947c-b0ff-43f8-bb62-658f23a54db8Latest
701ff947c-b0ff-43f8-bb62-658f23a54db8Old
100283453b-b6e8-4bc2-ae2d-1ed5f7b9f57aLatest
100283453b-b6e8-4bc2-ae2d-1ed5f7b9f57aOld
1302c39a7d-a369-41b4-9128-7c5103127bfcLatest
1302c39a7d-a369-41b4-9128-7c5103127bfcOld
90319f4f1-d21d-4d7f-b573-3f43248a5c21Latest
90319f4f1-d21d-4d7f-b573-3f43248a5c21Old
150321fa1d-b136-4b5d-8399-e3c6cde868d6Latest
150321fa1d-b136-4b5d-8399-e3c6cde868d6Old
10034757c8-7c6a-4fd9-b5b9-e924f5ca76bcLatest
10034757c8-7c6a-4fd9-b5b9-e924f5ca76bcOld
70434bc24-29c0-4963-9e86-1705a24fd268Latest
70434bc24-29c0-4963-9e86-1705a24fd268Old
1004544604-5965-4e60-ac17-4454492bbeaeLatest
1004544604-5965-4e60-ac17-4454492bbeaeOld
7204548ef4-bb68-4bc7-aa12-f9bf17109289Old
7304548ef4-bb68-4bc7-aa12-f9bf17109289Latest
904f485ff-8cfd-4628-a414-1af6fb2545e4Latest
904f485ff-8cfd-4628-a414-1af6fb2545e4Old
200516499f-dffc-4110-a831-a842bdb52690Latest
200516499f-dffc-4110-a831-a842bdb52690Old
17053bed0a-5cfb-41c6-a736-e16b1495a01aLatest
17053bed0a-5cfb-41c6-a736-e16b1495a01aOld
110557dd16-c398-4106-b69d-8a308a7622f0Latest
110557dd16-c398-4106-b69d-8a308a7622f0Old
105dd6869-bae0-49c1-8ba3-5a84a7e04118Latest
105dd6869-bae0-49c1-8ba3-5a84a7e04118Old
1005e8cd49-9fc7-4911-a1e9-a00d1e80151fLatest
1005e8cd49-9fc7-4911-a1e9-a00d1e80151fOld
120763976f-4a7b-43da-894d-531c6d09c608Latest
120763976f-4a7b-43da-894d-531c6d09c608Old
1030837d753-97ac-46c8-b0f3-c5a1a0e3740aLatest

 

Output 

Total 355

For each subscription as below value of Role when added in Visual.

RoleIDLatest
18010506e1-e5cc-41b6-96c1-ea3a7cb9942cLatest
701ff947c-b0ff-43f8-bb62-658f23a54db8Latest
100283453b-b6e8-4bc2-ae2d-1ed5f7b9f57aLatest
1302c39a7d-a369-41b4-9128-7c5103127bfcLatest
90319f4f1-d21d-4d7f-b573-3f43248a5c21Latest
150321fa1d-b136-4b5d-8399-e3c6cde868d6Latest
10034757c8-7c6a-4fd9-b5b9-e924f5ca76bcLatest
70434bc24-29c0-4963-9e86-1705a24fd268Latest
1004544604-5965-4e60-ac17-4454492bbeaeLatest
7304548ef4-bb68-4bc7-aa12-f9bf17109289Latest
904f485ff-8cfd-4628-a414-1af6fb2545e4Latest
200516499f-dffc-4110-a831-a842bdb52690Latest
17053bed0a-5cfb-41c6-a736-e16b1495a01aLatest
110557dd16-c398-4106-b69d-8a308a7622f0Latest
105dd6869-bae0-49c1-8ba3-5a84a7e04118Latest
1005e8cd49-9fc7-4911-a1e9-a00d1e80151fLatest
120763976f-4a7b-43da-894d-531c6d09c608Latest
1030837d753-97ac-46c8-b0f3-c5a1a0e3740aLatest

 

 

11 REPLIES 11
Anonymous
Not applicable

 

Use below dax for your calculation.

Total=SUMX(DISTINCT( FILTER(table1,table1[Latest]="Latest")),table1[Roles])

 

amitchandak
Super User
Super User

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

Anonymous
Not applicable

@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

 

@Greg_Deckler 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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 

 

DakshGarg_0-1600192473475.png

 

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Total=SUMX(DISTINCT( FILTER(table1,table1[Latest]="Latest")),table1[Roles])
Fowmy
Super User
Super User

@Anonymous 

Try this measure, you can keep only the IDs in the table visual:

Total Roles = CALCULATE(SUM(Table2[Roles]), Table2[Latest] = "Latest")

 

Fowmy_0-1599679464011.png

 

________________________

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 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@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 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@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 

 

RolesIDLatest
18010506e1-e5cc-41b6-96c1-ea3a7cb9942cLatest
18010506e1-e5cc-41b6-96c1-ea3a7cb9942cLatest
18010506e1-e5cc-41b6-96c1-ea3a7cb9942cOld
701ff947c-b0ff-43f8-bb62-658f23a54db8Latest
701ff947c-b0ff-43f8-bb62-658f23a54db8Latest
701ff947c-b0ff-43f8-bb62-658f23a54db8Old
100283453b-b6e8-4bc2-ae2d-1ed5f7b9f57aLatest
100283453b-b6e8-4bc2-ae2d-1ed5f7b9f57aOld
1302c39a7d-a369-41b4-9128-7c5103127bfcLatest
1302c39a7d-a369-41b4-9128-7c5103127bfcOld
90319f4f1-d21d-4d7f-b573-3f43248a5c21Latest
90319f4f1-d21d-4d7f-b573-3f43248a5c21Old
150321fa1d-b136-4b5d-8399-e3c6cde868d6Latest
150321fa1d-b136-4b5d-8399-e3c6cde868d6Old
10034757c8-7c6a-4fd9-b5b9-e924f5ca76bcLatest
10034757c8-7c6a-4fd9-b5b9-e924f5ca76bcOld
70434bc24-29c0-4963-9e86-1705a24fd268Latest
70434bc24-29c0-4963-9e86-1705a24fd268Latest
70434bc24-29c0-4963-9e86-1705a24fd268Old
1004544604-5965-4e60-ac17-4454492bbeaeLatest
1004544604-5965-4e60-ac17-4454492bbeaeOld
7204548ef4-bb68-4bc7-aa12-f9bf17109289Old
7304548ef4-bb68-4bc7-aa12-f9bf17109289Latest
904f485ff-8cfd-4628-a414-1af6fb2545e4Latest
904f485ff-8cfd-4628-a414-1af6fb2545e4Old
200516499f-dffc-4110-a831-a842bdb52690Latest
200516499f-dffc-4110-a831-a842bdb52690Old
17053bed0a-5cfb-41c6-a736-e16b1495a01aLatest
17053bed0a-5cfb-41c6-a736-e16b1495a01aLatest
17053bed0a-5cfb-41c6-a736-e16b1495a01aOld
110557dd16-c398-4106-b69d-8a308a7622f0Latest
110557dd16-c398-4106-b69d-8a308a7622f0Old
105dd6869-bae0-49c1-8ba3-5a84a7e04118Latest
105dd6869-bae0-49c1-8ba3-5a84a7e04118Latest
105dd6869-bae0-49c1-8ba3-5a84a7e04118Old
1005e8cd49-9fc7-4911-a1e9-a00d1e80151fLatest
1005e8cd49-9fc7-4911-a1e9-a00d1e80151fOld
120763976f-4a7b-43da-894d-531c6d09c608Latest
120763976f-4a7b-43da-894d-531c6d09c608Old
1030837d753-97ac-46c8-b0f3-c5a1a0e3740aLatest

 

 

@amitchandak 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors