topic Re: Measure to calculate sum based on distinct value from another column in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1372973#M25039 Total=SUMX(DISTINCT( FILTER(table1,table1[Latest]="Latest")),table1[Roles]) Tue, 15 Sep 2020 18:26:12 GMT Anonymous 2020-09-15T18:26:12Z Measure to calculate sum based on distinct value from another column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1359175#M24593 <P>I want to create a measure to calculate sum of roles based on distinct IDs and Latest = "Latest"</P><P><SPAN>I tried the below expression but not getting the desired output&nbsp;</SPAN></P><P><SPAN>Total = CALCULATE(SUM('table1'[Roles]),</SPAN><SPAN>'table1'[Latest]="Latest",DISTINCT(</SPAN><SPAN>'table1'[Id])</SPAN></P><DIV><DIV>&nbsp;</DIV><DIV>&nbsp;</DIV></DIV><TABLE><TBODY><TR><TD>Roles</TD><TD>ID</TD><TD>Latest</TD></TR><TR><TD>18</TD><TD>010506e1-e5cc-41b6-96c1-ea3a7cb9942c</TD><TD>Latest</TD></TR><TR><TD>18</TD><TD>010506e1-e5cc-41b6-96c1-ea3a7cb9942c</TD><TD>Old</TD></TR><TR><TD>7</TD><TD>01ff947c-b0ff-43f8-bb62-658f23a54db8</TD><TD>Latest</TD></TR><TR><TD>7</TD><TD>01ff947c-b0ff-43f8-bb62-658f23a54db8</TD><TD>Old</TD></TR><TR><TD>10</TD><TD>0283453b-b6e8-4bc2-ae2d-1ed5f7b9f57a</TD><TD>Latest</TD></TR><TR><TD>10</TD><TD>0283453b-b6e8-4bc2-ae2d-1ed5f7b9f57a</TD><TD>Old</TD></TR><TR><TD>13</TD><TD>02c39a7d-a369-41b4-9128-7c5103127bfc</TD><TD>Latest</TD></TR><TR><TD>13</TD><TD>02c39a7d-a369-41b4-9128-7c5103127bfc</TD><TD>Old</TD></TR><TR><TD>9</TD><TD>0319f4f1-d21d-4d7f-b573-3f43248a5c21</TD><TD>Latest</TD></TR><TR><TD>9</TD><TD>0319f4f1-d21d-4d7f-b573-3f43248a5c21</TD><TD>Old</TD></TR><TR><TD>15</TD><TD>0321fa1d-b136-4b5d-8399-e3c6cde868d6</TD><TD>Latest</TD></TR><TR><TD>15</TD><TD>0321fa1d-b136-4b5d-8399-e3c6cde868d6</TD><TD>Old</TD></TR><TR><TD>10</TD><TD>034757c8-7c6a-4fd9-b5b9-e924f5ca76bc</TD><TD>Latest</TD></TR><TR><TD>10</TD><TD>034757c8-7c6a-4fd9-b5b9-e924f5ca76bc</TD><TD>Old</TD></TR><TR><TD>7</TD><TD>0434bc24-29c0-4963-9e86-1705a24fd268</TD><TD>Latest</TD></TR><TR><TD>7</TD><TD>0434bc24-29c0-4963-9e86-1705a24fd268</TD><TD>Old</TD></TR><TR><TD>10</TD><TD>04544604-5965-4e60-ac17-4454492bbeae</TD><TD>Latest</TD></TR><TR><TD>10</TD><TD>04544604-5965-4e60-ac17-4454492bbeae</TD><TD>Old</TD></TR><TR><TD>72</TD><TD>04548ef4-bb68-4bc7-aa12-f9bf17109289</TD><TD>Old</TD></TR><TR><TD>73</TD><TD>04548ef4-bb68-4bc7-aa12-f9bf17109289</TD><TD>Latest</TD></TR><TR><TD>9</TD><TD>04f485ff-8cfd-4628-a414-1af6fb2545e4</TD><TD>Latest</TD></TR><TR><TD>9</TD><TD>04f485ff-8cfd-4628-a414-1af6fb2545e4</TD><TD>Old</TD></TR><TR><TD>20</TD><TD>0516499f-dffc-4110-a831-a842bdb52690</TD><TD>Latest</TD></TR><TR><TD>20</TD><TD>0516499f-dffc-4110-a831-a842bdb52690</TD><TD>Old</TD></TR><TR><TD>17</TD><TD>053bed0a-5cfb-41c6-a736-e16b1495a01a</TD><TD>Latest</TD></TR><TR><TD>17</TD><TD>053bed0a-5cfb-41c6-a736-e16b1495a01a</TD><TD>Old</TD></TR><TR><TD>11</TD><TD>0557dd16-c398-4106-b69d-8a308a7622f0</TD><TD>Latest</TD></TR><TR><TD>11</TD><TD>0557dd16-c398-4106-b69d-8a308a7622f0</TD><TD>Old</TD></TR><TR><TD>1</TD><TD>05dd6869-bae0-49c1-8ba3-5a84a7e04118</TD><TD>Latest</TD></TR><TR><TD>1</TD><TD>05dd6869-bae0-49c1-8ba3-5a84a7e04118</TD><TD>Old</TD></TR><TR><TD>10</TD><TD>05e8cd49-9fc7-4911-a1e9-a00d1e80151f</TD><TD>Latest</TD></TR><TR><TD>10</TD><TD>05e8cd49-9fc7-4911-a1e9-a00d1e80151f</TD><TD>Old</TD></TR><TR><TD>12</TD><TD>0763976f-4a7b-43da-894d-531c6d09c608</TD><TD>Latest</TD></TR><TR><TD>12</TD><TD>0763976f-4a7b-43da-894d-531c6d09c608</TD><TD>Old</TD></TR><TR><TD>103</TD><TD>0837d753-97ac-46c8-b0f3-c5a1a0e3740a</TD><TD>Latest</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Output&nbsp;</P><P>Total 355</P><P>For each subscription as below value of Role when added in Visual.</P><TABLE><TBODY><TR><TD>Role</TD><TD>ID</TD><TD>Latest</TD></TR><TR><TD>18</TD><TD>010506e1-e5cc-41b6-96c1-ea3a7cb9942c</TD><TD>Latest</TD></TR><TR><TD>7</TD><TD>01ff947c-b0ff-43f8-bb62-658f23a54db8</TD><TD>Latest</TD></TR><TR><TD>10</TD><TD>0283453b-b6e8-4bc2-ae2d-1ed5f7b9f57a</TD><TD>Latest</TD></TR><TR><TD>13</TD><TD>02c39a7d-a369-41b4-9128-7c5103127bfc</TD><TD>Latest</TD></TR><TR><TD>9</TD><TD>0319f4f1-d21d-4d7f-b573-3f43248a5c21</TD><TD>Latest</TD></TR><TR><TD>15</TD><TD>0321fa1d-b136-4b5d-8399-e3c6cde868d6</TD><TD>Latest</TD></TR><TR><TD>10</TD><TD>034757c8-7c6a-4fd9-b5b9-e924f5ca76bc</TD><TD>Latest</TD></TR><TR><TD>7</TD><TD>0434bc24-29c0-4963-9e86-1705a24fd268</TD><TD>Latest</TD></TR><TR><TD>10</TD><TD>04544604-5965-4e60-ac17-4454492bbeae</TD><TD>Latest</TD></TR><TR><TD>73</TD><TD>04548ef4-bb68-4bc7-aa12-f9bf17109289</TD><TD>Latest</TD></TR><TR><TD>9</TD><TD>04f485ff-8cfd-4628-a414-1af6fb2545e4</TD><TD>Latest</TD></TR><TR><TD>20</TD><TD>0516499f-dffc-4110-a831-a842bdb52690</TD><TD>Latest</TD></TR><TR><TD>17</TD><TD>053bed0a-5cfb-41c6-a736-e16b1495a01a</TD><TD>Latest</TD></TR><TR><TD>11</TD><TD>0557dd16-c398-4106-b69d-8a308a7622f0</TD><TD>Latest</TD></TR><TR><TD>1</TD><TD>05dd6869-bae0-49c1-8ba3-5a84a7e04118</TD><TD>Latest</TD></TR><TR><TD>10</TD><TD>05e8cd49-9fc7-4911-a1e9-a00d1e80151f</TD><TD>Latest</TD></TR><TR><TD>12</TD><TD>0763976f-4a7b-43da-894d-531c6d09c608</TD><TD>Latest</TD></TR><TR><TD>103</TD><TD>0837d753-97ac-46c8-b0f3-c5a1a0e3740a</TD><TD>Latest</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P> Wed, 09 Sep 2020 17:50:36 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1359175#M24593 Anonymous 2020-09-09T17:50:36Z Re: Measure to calculate sum based on distinct value from another column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1359334#M24599 <P>Anonymous</LI-USER>&nbsp;<BR /><BR />Try this measure, you can keep only the IDs in the table visual:<BR /><BR /></P><LI-CODE lang="markup">Total Roles = CALCULATE(SUM(Table2[Roles]), Table2[Latest] = "Latest")</LI-CODE><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P>________________________</P><P>If my answer was helpful, please consider <STRONG><I>Accept it as the solution</I></STRONG><I> to help the other members find it</I></P><P>Click on the <STRONG>Thumbs-Up icon </STRONG>if you like this reply <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P><A href="https://www.youtube.com/channel/UCKwBEguA8IlBubIobaOormg?sub_confirmation=1" target="_blank"><FONT color="blue">YouTube</FONT></A>&nbsp; <A href="https://linkedin.com/in/fowmy" target="_blank"><FONT color="blue">LinkedIn</FONT></A></P> Wed, 09 Sep 2020 19:24:38 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1359334#M24599 Fowmy 2020-09-09T19:24:38Z Re: Measure to calculate sum based on distinct value from another column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1359378#M24604 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="52518" data-lia-user-login="Fowmy" class="lia-mention lia-mention-user">Fowmy</a>&nbsp;I also need to add this measure on the card visual ..&nbsp;</P><P>&nbsp;</P><P>Can we add any logic to take sum for distinct ids and latest field .</P> Wed, 09 Sep 2020 19:48:02 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1359378#M24604 Anonymous 2020-09-09T19:48:02Z Re: Measure to calculate sum based on distinct value from another column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1359454#M24608 <P>Anonymous</a>&nbsp;</P><P>&nbsp;</P><P>As per the sample data you provided, the measure I shared should work ona card visual as well.&nbsp;<BR /><BR /></P><P>You needn't take distinct as you&nbsp;</P> Wed, 09 Sep 2020 20:32:29 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1359454#M24608 Fowmy 2020-09-09T20:32:29Z Re: Measure to calculate sum based on distinct value from another column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1360175#M24633 <P>Anonymous</a>&nbsp;</P> <P>&nbsp;</P> <P>CALCULATE(Sum('table1'[Roles]),'table1'[Latest]="Latest")</P> <P>Sumx(Sumamrize(Filter('table1','table1'[Latest]="Latest"),'table1'[Roles]),[Roles])</P> <P>&nbsp;</P> <P>Do you need add roles or count</P> <P>CALCULATE(count('table1'[Roles]),'table1'[Latest]="Latest")</P> <P>countX(Sumamrize(Filter('table1','table1'[Latest]="Latest"),'table1'[Roles]),[Roles])</P> Thu, 10 Sep 2020 04:31:01 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1360175#M24633 amitchandak 2020-09-10T04:31:01Z Re: Measure to calculate sum based on distinct value from another column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1361955#M24679 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="52518" data-lia-user-login="Fowmy" class="lia-mention lia-mention-user">Fowmy</a>&nbsp; I found the catch on the sample data . There could be duplicate ID with both marked as Latest on the cloumn.&nbsp;</P><P>&nbsp;</P><P>For the duplicate ID, I just want to consider one row for Role Summation.&nbsp;</P><P>&nbsp;</P><P>Sum of Roles where Distinct ID and Latest = Latest&nbsp;</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Roles</TD><TD>ID</TD><TD>Latest</TD></TR><TR><TD>18</TD><TD>010506e1-e5cc-41b6-96c1-ea3a7cb9942c</TD><TD>Latest</TD></TR><TR><TD>18</TD><TD>010506e1-e5cc-41b6-96c1-ea3a7cb9942c</TD><TD>Latest</TD></TR><TR><TD>18</TD><TD>010506e1-e5cc-41b6-96c1-ea3a7cb9942c</TD><TD>Old</TD></TR><TR><TD>7</TD><TD>01ff947c-b0ff-43f8-bb62-658f23a54db8</TD><TD>Latest</TD></TR><TR><TD>7</TD><TD>01ff947c-b0ff-43f8-bb62-658f23a54db8</TD><TD>Latest</TD></TR><TR><TD>7</TD><TD>01ff947c-b0ff-43f8-bb62-658f23a54db8</TD><TD>Old</TD></TR><TR><TD>10</TD><TD>0283453b-b6e8-4bc2-ae2d-1ed5f7b9f57a</TD><TD>Latest</TD></TR><TR><TD>10</TD><TD>0283453b-b6e8-4bc2-ae2d-1ed5f7b9f57a</TD><TD>Old</TD></TR><TR><TD>13</TD><TD>02c39a7d-a369-41b4-9128-7c5103127bfc</TD><TD>Latest</TD></TR><TR><TD>13</TD><TD>02c39a7d-a369-41b4-9128-7c5103127bfc</TD><TD>Old</TD></TR><TR><TD>9</TD><TD>0319f4f1-d21d-4d7f-b573-3f43248a5c21</TD><TD>Latest</TD></TR><TR><TD>9</TD><TD>0319f4f1-d21d-4d7f-b573-3f43248a5c21</TD><TD>Old</TD></TR><TR><TD>15</TD><TD>0321fa1d-b136-4b5d-8399-e3c6cde868d6</TD><TD>Latest</TD></TR><TR><TD>15</TD><TD>0321fa1d-b136-4b5d-8399-e3c6cde868d6</TD><TD>Old</TD></TR><TR><TD>10</TD><TD>034757c8-7c6a-4fd9-b5b9-e924f5ca76bc</TD><TD>Latest</TD></TR><TR><TD>10</TD><TD>034757c8-7c6a-4fd9-b5b9-e924f5ca76bc</TD><TD>Old</TD></TR><TR><TD>7</TD><TD>0434bc24-29c0-4963-9e86-1705a24fd268</TD><TD>Latest</TD></TR><TR><TD>7</TD><TD>0434bc24-29c0-4963-9e86-1705a24fd268</TD><TD>Latest</TD></TR><TR><TD>7</TD><TD>0434bc24-29c0-4963-9e86-1705a24fd268</TD><TD>Old</TD></TR><TR><TD>10</TD><TD>04544604-5965-4e60-ac17-4454492bbeae</TD><TD>Latest</TD></TR><TR><TD>10</TD><TD>04544604-5965-4e60-ac17-4454492bbeae</TD><TD>Old</TD></TR><TR><TD>72</TD><TD>04548ef4-bb68-4bc7-aa12-f9bf17109289</TD><TD>Old</TD></TR><TR><TD>73</TD><TD>04548ef4-bb68-4bc7-aa12-f9bf17109289</TD><TD>Latest</TD></TR><TR><TD>9</TD><TD>04f485ff-8cfd-4628-a414-1af6fb2545e4</TD><TD>Latest</TD></TR><TR><TD>9</TD><TD>04f485ff-8cfd-4628-a414-1af6fb2545e4</TD><TD>Old</TD></TR><TR><TD>20</TD><TD>0516499f-dffc-4110-a831-a842bdb52690</TD><TD>Latest</TD></TR><TR><TD>20</TD><TD>0516499f-dffc-4110-a831-a842bdb52690</TD><TD>Old</TD></TR><TR><TD>17</TD><TD>053bed0a-5cfb-41c6-a736-e16b1495a01a</TD><TD>Latest</TD></TR><TR><TD>17</TD><TD>053bed0a-5cfb-41c6-a736-e16b1495a01a</TD><TD>Latest</TD></TR><TR><TD>17</TD><TD>053bed0a-5cfb-41c6-a736-e16b1495a01a</TD><TD>Old</TD></TR><TR><TD>11</TD><TD>0557dd16-c398-4106-b69d-8a308a7622f0</TD><TD>Latest</TD></TR><TR><TD>11</TD><TD>0557dd16-c398-4106-b69d-8a308a7622f0</TD><TD>Old</TD></TR><TR><TD>1</TD><TD>05dd6869-bae0-49c1-8ba3-5a84a7e04118</TD><TD>Latest</TD></TR><TR><TD>1</TD><TD>05dd6869-bae0-49c1-8ba3-5a84a7e04118</TD><TD>Latest</TD></TR><TR><TD>1</TD><TD>05dd6869-bae0-49c1-8ba3-5a84a7e04118</TD><TD>Old</TD></TR><TR><TD>10</TD><TD>05e8cd49-9fc7-4911-a1e9-a00d1e80151f</TD><TD>Latest</TD></TR><TR><TD>10</TD><TD>05e8cd49-9fc7-4911-a1e9-a00d1e80151f</TD><TD>Old</TD></TR><TR><TD>12</TD><TD>0763976f-4a7b-43da-894d-531c6d09c608</TD><TD>Latest</TD></TR><TR><TD>12</TD><TD>0763976f-4a7b-43da-894d-531c6d09c608</TD><TD>Old</TD></TR><TR><TD>103</TD><TD>0837d753-97ac-46c8-b0f3-c5a1a0e3740a</TD><TD>Latest</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="148838" data-lia-user-login="amitchandak" class="lia-mention lia-mention-user">amitchandak</a>&nbsp;</P> Thu, 10 Sep 2020 15:18:09 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1361955#M24679 Anonymous 2020-09-10T15:18:09Z Re: Measure to calculate sum based on distinct value from another column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1366037#M24791 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="148838" data-lia-user-login="amitchandak" class="lia-mention lia-mention-user">amitchandak</a>&nbsp; I tried these but it is adding duplicate count as well since the ID can be duplicate ..&nbsp;</P><P>&nbsp;</P><P>I want to claculate Sum of roles&nbsp; = where ID is ditinct and Latest column = Latest.</P><P>&nbsp;</P><P>Sample data in below comments</P><P>&nbsp;</P><P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="313" data-lia-user-login="Greg_Deckler" class="lia-mention lia-mention-user">Greg_Deckler</a>&nbsp;</P> Sat, 12 Sep 2020 10:17:36 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1366037#M24791 Anonymous 2020-09-12T10:17:36Z Re: Measure to calculate sum based on distinct value from another column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1366907#M24824 <P>Anonymous</a>&nbsp;- I believe what you want is the following:</P> <LI-CODE lang="markup">Measure = SUMX( SUMMARIZE( FILTER('Table','Table'[Latest]="Latest"), 'Table'[ID]), "__Roles",AVERAGE([Roles]) ), [__Roles] )</LI-CODE> <P>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.</P> Sun, 13 Sep 2020 16:27:55 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1366907#M24824 Greg_Deckler 2020-09-13T16:27:55Z Re: Measure to calculate sum based on distinct value from another column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1366997#M24830 <P>&nbsp;</P><P>Use below dax for your calculation.</P><LI-CODE lang="markup">Total=SUMX(DISTINCT( FILTER(table1,table1[Latest]="Latest")),table1[Roles])</LI-CODE><P>&nbsp;</P> Sun, 13 Sep 2020 18:28:04 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1366997#M24830 Anonymous 2020-09-13T18:28:04Z Re: Measure to calculate sum based on distinct value from another column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1372910#M25036 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="313" data-lia-user-login="Greg_Deckler" class="lia-mention lia-mention-user">Greg_Deckler</a>&nbsp; &nbsp;Getting Syntax error&nbsp;</P><P>&nbsp;</P><P>Wont averaging out cause the issues with calculations as there could be multiple repeats not necessarly 1 duplicate in ID Column&nbsp;</P><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P>Can t we have expression to filter first that Latest then have distinct IDs and corresponding do Addition of roles .</P> Tue, 15 Sep 2020 17:56:47 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1372910#M25036 Anonymous 2020-09-15T17:56:47Z Re: Measure to calculate sum based on distinct value from another column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1372973#M25039 Total=SUMX(DISTINCT( FILTER(table1,table1[Latest]="Latest")),table1[Roles]) Tue, 15 Sep 2020 18:26:12 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1372973#M25039 Anonymous 2020-09-15T18:26:12Z Re: Measure to calculate sum based on distinct value from another column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1372977#M25040 <P>Anonymous</a>&nbsp;Syntax, had a stray )</P> <LI-CODE lang="markup">Measure = SUMX( SUMMARIZE( FILTER('Table','Table'[Latest]="Latest"), 'Table'[ID], "__Roles",AVERAGE([Roles]) ), [__Roles] )</LI-CODE> <P>&nbsp;</P> <P>&nbsp;</P> Tue, 15 Sep 2020 18:27:50 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-calculate-sum-based-on-distinct-value-from-another/m-p/1372977#M25040 Greg_Deckler 2020-09-15T18:27:50Z