topic Re: Issue in defining a measure in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994716#M155126 <P>Dear&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="118660" data-lia-user-login="rajendraongole1" class="lia-mention lia-mention-user">rajendraongole1</a>&nbsp;,</P><P>thank you a lot for your answer. Unfortunately what you achieved is my wrong result.</P><P>What I want to achieve through a measure or a calculated column is the following (Desired Result):</P><TABLE><TBODY><TR><TD>Id</TD><TD>Date</TD><TD>S_Cost</TD><TD>D_Cost</TD><TD>Tot_D_Cost</TD><TD>DESIRED RESULT</TD></TR><TR><TD>1</TD><TD>01/01/2024</TD><TD>500</TD><TD>1500</TD><TD>3300</TD><TD>646,5517241</TD></TR><TR><TD>1</TD><TD>01/01/2024</TD><TD>500</TD><TD>1800</TD><TD>3300</TD><TD>775,862069</TD></TR><TR><TD>1</TD><TD>03/01/2024</TD><TD>2000</TD><TD>2500</TD><TD>2500</TD><TD>1077,586207</TD></TR><TR><TD>2</TD><TD>01/02/2024</TD><TD>500</TD><TD>2100</TD><TD>3300</TD><TD>692,3076923</TD></TR><TR><TD>2</TD><TD>02/02/2024</TD><TD>500</TD><TD>2800</TD><TD>3300</TD><TD>923,0769231</TD></TR><TR><TD>2</TD><TD>10/02/2024</TD><TD>2000</TD><TD>4200</TD><TD>2500</TD><TD>1384,615385</TD></TR></TBODY></TABLE> Sun, 16 Jun 2024 08:40:58 GMT LorenzoC 2024-06-16T08:40:58Z Issue in defining a measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994433#M155097 <P>Dear All,</P><P>i have the following dataset:</P><TABLE><TBODY><TR><TD>Id</TD><TD>Date</TD><TD>S_Cost</TD><TD>D_Cost</TD><TD>Tot_D_Cost</TD></TR><TR><TD>1</TD><TD>01/01/2024</TD><TD>500</TD><TD>1500</TD><TD>3300</TD></TR><TR><TD>1</TD><TD>01/01/2024</TD><TD>500</TD><TD>1800</TD><TD>3300</TD></TR><TR><TD>1</TD><TD>03/01/2024</TD><TD>2000</TD><TD>2500</TD><TD>2500</TD></TR><TR><TD>2</TD><TD>01/02/2024</TD><TD>500</TD><TD>2100</TD><TD>3300</TD></TR><TR><TD>2</TD><TD>02/02/2024</TD><TD>500</TD><TD>2800</TD><TD>3300</TD></TR><TR><TD>2</TD><TD>10/02/2024</TD><TD>2000</TD><TD>4200</TD><TD>2500</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Wha I need is define a MEASURE (or at least a calculated column) which splits the value of TOTAL DISTINCT S_Cost with reference to the Id and Date.</P><P>&nbsp;</P><P>Firstly I created two measures:&nbsp;</P><DIV><DIV><SPAN>DISTINCT_S_Cost =</SPAN> <SPAN>SUMX</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>SUMMARIZE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>'Tabella 3 (Foglio2)'</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>'Tabella 3 (Foglio2)'</SPAN><SPAN>[Id]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>'Tabella 3 (Foglio2)'</SPAN><SPAN>[S_Cost]</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>'Tabella 3 (Foglio2)'</SPAN><SPAN>[S_Cost]</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>and&nbsp;</SPAN></DIV><DIV>&nbsp;</DIV><DIV><DIV><DIV><SPAN>DISTINCT_TOTAL_D_Cost =</SPAN> <SPAN>SUMX</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>SUMMARIZE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>'Tabella 3 (Foglio2)'</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>'Tabella 3 (Foglio2)'</SPAN><SPAN>[Id]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>'Tabella 3 (Foglio2)'</SPAN><SPAN>[Tot_D_Cost]</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>'Tabella 3 (Foglio2)'</SPAN><SPAN>[Tot_D_Cost]</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV>Then I created the my FINAL MEASURE, which was supposed to give my desired result:</DIV><DIV><DIV><DIV><SPAN>Final_Measure =</SPAN> <SPAN>DIVIDE</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>'Tabella 3 (Foglio2)'</SPAN><SPAN>[D_Cost]</SPAN><SPAN>),</SPAN><SPAN>[DISTINCT_TOTAL_D_Cost]</SPAN><SPAN>)* </SPAN><SPAN>[DISTINCT_S_Cost]</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>However, this does not work.</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>The following are my wrong result with my final measure and my desired result which i cannot achieve:</SPAN></DIV><DIV><TABLE><TBODY><TR><TD>Id</TD><TD>Date</TD><TD>S_Cost</TD><TD>D_Cost</TD><TD>Tot_D_Cost</TD><TD>DESIRED RESULT</TD></TR><TR><TD>1</TD><TD>01/01/2024</TD><TD>500</TD><TD>1500</TD><TD>3300</TD><TD>646,5517241</TD></TR><TR><TD>1</TD><TD>01/01/2024</TD><TD>500</TD><TD>1800</TD><TD>3300</TD><TD>775,862069</TD></TR><TR><TD>1</TD><TD>03/01/2024</TD><TD>2000</TD><TD>2500</TD><TD>2500</TD><TD>1077,586207</TD></TR><TR><TD>2</TD><TD>01/02/2024</TD><TD>500</TD><TD>2100</TD><TD>3300</TD><TD>692,3076923</TD></TR><TR><TD>2</TD><TD>02/02/2024</TD><TD>500</TD><TD>2800</TD><TD>3300</TD><TD>923,0769231</TD></TR><TR><TD>2</TD><TD>10/02/2024</TD><TD>2000</TD><TD>4200</TD><TD>2500</TD><TD>1384,615385</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Wrong result:</P><img /><P>&nbsp;</P><P>&nbsp;</P><P>Do you think I can adjust this final measure to reach my desired result?<BR />Thank you a lot</P><P>Lorenzo</P></DIV><DIV>&nbsp;</DIV></DIV></DIV></DIV></DIV></DIV><P>&nbsp;</P> Sat, 15 Jun 2024 18:16:47 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994433#M155097 LorenzoC 2024-06-15T18:16:47Z Re: Issue in defining a measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994443#M155098 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="758047" data-lia-user-login="LorenzoC" class="lia-mention lia-mention-user">LorenzoC</a>&nbsp;- Creating calculated columns for&nbsp;S_Cost and Tot_D_Cost per Id and Date</P><P>&nbsp;</P><P>Calculated column for Total S cost distinct</P><DIV><DIV><SPAN>Distinct_Total_S_Cost = </SPAN></DIV><DIV><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>SUM</SPAN><SPAN>('Desired'[S_Cost]),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>ALLEXCEPT</SPAN><SPAN>('Desired', 'Desired'[Id], 'Desired'[Date])</SPAN></DIV><DIV><SPAN>)</SPAN></DIV></DIV><P>&nbsp;</P><P>Another calculated column :&nbsp;</P><DIV><DIV><SPAN>Distinct_Total_D_Cost =</SPAN></DIV><DIV><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>'Desired'</SPAN><SPAN>[Tot_D_Cost]</SPAN><SPAN>),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>ALLEXCEPT</SPAN><SPAN>(</SPAN><SPAN>'Desired'</SPAN><SPAN>, </SPAN><SPAN>'Desired'</SPAN><SPAN>[Id]</SPAN><SPAN>, </SPAN><SPAN>'Desired'</SPAN><SPAN>[Date]</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>)</SPAN></DIV></DIV><P>&nbsp;</P><P>create a calculated column to calculate the the distinct totals as below:</P><P>&nbsp;</P><DIV><DIV><SPAN>Final_Result =</SPAN></DIV><DIV><SPAN>DIVIDE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>'Desired'</SPAN><SPAN>[D_Cost]</SPAN><SPAN>,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>'Desired'</SPAN><SPAN>[Distinct_Total_D_Cost]</SPAN></DIV><DIV><SPAN>) * </SPAN><SPAN>'Desired'</SPAN><SPAN>[Distinct_Total_S_Cost]</SPAN></DIV></DIV><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P>&nbsp;</P><P>In report:</P><P><img /></P><P>&nbsp;</P><P><FONT color="#000080">Did I answer your question? Mark my post as a solution! This will help others on the forum!</FONT><BR /><FONT color="#000080">Appreciate your Kudos!!</FONT></P> Sat, 15 Jun 2024 18:56:54 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994443#M155098 rajendraongole1 2024-06-15T18:56:54Z Re: Issue in defining a measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994716#M155126 <P>Dear&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="118660" data-lia-user-login="rajendraongole1" class="lia-mention lia-mention-user">rajendraongole1</a>&nbsp;,</P><P>thank you a lot for your answer. Unfortunately what you achieved is my wrong result.</P><P>What I want to achieve through a measure or a calculated column is the following (Desired Result):</P><TABLE><TBODY><TR><TD>Id</TD><TD>Date</TD><TD>S_Cost</TD><TD>D_Cost</TD><TD>Tot_D_Cost</TD><TD>DESIRED RESULT</TD></TR><TR><TD>1</TD><TD>01/01/2024</TD><TD>500</TD><TD>1500</TD><TD>3300</TD><TD>646,5517241</TD></TR><TR><TD>1</TD><TD>01/01/2024</TD><TD>500</TD><TD>1800</TD><TD>3300</TD><TD>775,862069</TD></TR><TR><TD>1</TD><TD>03/01/2024</TD><TD>2000</TD><TD>2500</TD><TD>2500</TD><TD>1077,586207</TD></TR><TR><TD>2</TD><TD>01/02/2024</TD><TD>500</TD><TD>2100</TD><TD>3300</TD><TD>692,3076923</TD></TR><TR><TD>2</TD><TD>02/02/2024</TD><TD>500</TD><TD>2800</TD><TD>3300</TD><TD>923,0769231</TD></TR><TR><TD>2</TD><TD>10/02/2024</TD><TD>2000</TD><TD>4200</TD><TD>2500</TD><TD>1384,615385</TD></TR></TBODY></TABLE> Sun, 16 Jun 2024 08:40:58 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994716#M155126 LorenzoC 2024-06-16T08:40:58Z Re: Issue in defining a measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994739#M155132 <P>Hello&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="758047" data-lia-user-login="LorenzoC" class="lia-mention lia-mention-user">LorenzoC</a>&nbsp;,&nbsp;<BR /><BR />Can you please explain how you calculated your desired result? To have better understanding of the filter context as your explanation is not so clear in the original post.&nbsp;<BR />Thank you.</P> Sun, 16 Jun 2024 09:41:00 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994739#M155132 Moetazzahran 2024-06-16T09:41:00Z Re: Issue in defining a measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994753#M155133 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="758047" data-lia-user-login="LorenzoC" class="lia-mention lia-mention-user">LorenzoC</a>&nbsp;- Can you help to understand with one record how we are getting desired result 646.55 for 1 record?</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Id</TD><TD>Date</TD><TD>S_Cost</TD><TD>D_Cost</TD><TD>Tot_D_Cost</TD><TD>DESIRED RESULT</TD></TR><TR><TD>1</TD><TD>01/01/2024</TD><TD>500</TD><TD>1500</TD><TD>3300</TD><TD>646.5517241</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I have applied with&nbsp;formula dividing D_Cost by S_Cost and Tot_D_Cost as multiply.</P><P><img /></P><DIV><DIV><SPAN>Desired_Result = </SPAN><SPAN>DIVIDE</SPAN><SPAN>([DC] * </SPAN><SPAN>100</SPAN><SPAN>, [SC]) * </SPAN><SPAN>DIVIDE</SPAN><SPAN>([TDC], </SPAN><SPAN>100</SPAN><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><FONT color="#000080"><STRONG>Did I answer your question? Mark my post as a solution! This will help others on the forum!</STRONG></FONT><BR /><FONT color="#000080"><STRONG>Appreciate your Kudos!!</STRONG></FONT></DIV></DIV> Sun, 16 Jun 2024 10:07:52 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994753#M155133 rajendraongole1 2024-06-16T10:07:52Z Re: Issue in defining a measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994770#M155135 <P>Dear&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="118660" data-lia-user-login="rajendraongole1" class="lia-mention lia-mention-user">rajendraongole1</a>&nbsp;,</P><P>the formula i used (in excel) for obtaining my desired result is:</P><P>(D_Cost / SUM Distinct Tot_D_Cost) * SUM_Distinct S_Cost</P><P>&nbsp;</P><P>which in table is:</P><TABLE><TBODY><TR><TD>Id</TD><TD>Date</TD><TD>S_Cost</TD><TD>D_Cost</TD><TD>Tot_D_Cost</TD><TD>DESIRED RESULT</TD><TD>Formula for DESIRED RESULT</TD></TR><TR><TD>1</TD><TD>01/01/2024</TD><TD>500</TD><TD>1500</TD><TD>3300</TD><TD>646,5517241</TD><TD>(1500/5800)*2500</TD></TR><TR><TD>1</TD><TD>01/01/2024</TD><TD>500</TD><TD>1800</TD><TD>3300</TD><TD>775,862069</TD><TD>(1800/5800)*2500</TD></TR><TR><TD>1</TD><TD>03/01/2024</TD><TD>2000</TD><TD>2500</TD><TD>2500</TD><TD>1077,586207</TD><TD>(2500/5800)*2500</TD></TR><TR><TD>2</TD><TD>01/02/2024</TD><TD>500</TD><TD>2100</TD><TD>3300</TD><TD>692,3076923</TD><TD>(2100/9100)*3000</TD></TR><TR><TD>2</TD><TD>02/02/2024</TD><TD>500</TD><TD>2800</TD><TD>3300</TD><TD>923,0769231</TD><TD>(2800/9100)*3000</TD></TR><TR><TD>2</TD><TD>10/02/2024</TD><TD>2000</TD><TD>4200</TD><TD>2500</TD><TD>1384,615385</TD><TD>(4200/9100)*3000</TD></TR></TBODY></TABLE> Sun, 16 Jun 2024 10:50:11 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Issue-in-defining-a-measure/m-p/3994770#M155135 LorenzoC 2024-06-16T10:50:11Z