https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DIVIDE-for-just-for-specific-codes/m-p/2190138#M51173 <P>I have a dimensional table that shows me for each product the product group:</P><TABLE border="1"><TBODY><TR><TD>Product</TD><TD>Product Group</TD></TR><TR><TD>A1</TD><TD>A</TD></TR><TR><TD>A2</TD><TD>A</TD></TR><TR><TD>B1</TD><TD>B</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Then I have a sales table, that shows me the sales per Product and the last table with the budget per Product.</P><P>Now I would like to divide each sale on the product level versus the budget, but somehow the aggregation on the department level doesn't work.</P><TABLE><TBODY><TR><TD>Department</TD><TD>Product</TD><TD>Sales</TD><TD>Budget</TD><TD>DIVIDE</TD></TR><TR><TD>A</TD><TD>A1</TD><TD>500</TD><TD>500</TD><TD>100%</TD></TR><TR><TD>A</TD><TD>A2</TD><TD>500</TD><TD>500</TD><TD>100%</TD></TR><TR><TD>A</TD><TD>A3</TD><TD>500</TD><TD>&nbsp;</TD><TD>0%</TD></TR><TR><TD>B</TD><TD>B1</TD><TD>250</TD><TD>300</TD><TD>83%</TD></TR><TR><TD>&nbsp;</TD><TD>Total</TD><TD>1750</TD><TD>1300</TD><TD>135%</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>Right</TD><TD>1250</TD><TD>1300</TD><TD>96%</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>There should be 96% as a result shown but I got 135%.</P><P>Do I need an iterative function here? Like a SUMX?</P> Mon, 15 Nov 2021 11:12:37 GMT joshua1990 2021-11-15T11:12:37Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DIVIDE-for-just-for-specific-codes/m-p/2190138#M51173 <P>I have a dimensional table that shows me for each product the product group:</P><TABLE border="1"><TBODY><TR><TD>Product</TD><TD>Product Group</TD></TR><TR><TD>A1</TD><TD>A</TD></TR><TR><TD>A2</TD><TD>A</TD></TR><TR><TD>B1</TD><TD>B</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Then I have a sales table, that shows me the sales per Product and the last table with the budget per Product.</P><P>Now I would like to divide each sale on the product level versus the budget, but somehow the aggregation on the department level doesn't work.</P><TABLE><TBODY><TR><TD>Department</TD><TD>Product</TD><TD>Sales</TD><TD>Budget</TD><TD>DIVIDE</TD></TR><TR><TD>A</TD><TD>A1</TD><TD>500</TD><TD>500</TD><TD>100%</TD></TR><TR><TD>A</TD><TD>A2</TD><TD>500</TD><TD>500</TD><TD>100%</TD></TR><TR><TD>A</TD><TD>A3</TD><TD>500</TD><TD>&nbsp;</TD><TD>0%</TD></TR><TR><TD>B</TD><TD>B1</TD><TD>250</TD><TD>300</TD><TD>83%</TD></TR><TR><TD>&nbsp;</TD><TD>Total</TD><TD>1750</TD><TD>1300</TD><TD>135%</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>Right</TD><TD>1250</TD><TD>1300</TD><TD>96%</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>There should be 96% as a result shown but I got 135%.</P><P>Do I need an iterative function here? Like a SUMX?</P> Mon, 15 Nov 2021 11:12:37 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DIVIDE-for-just-for-specific-codes/m-p/2190138#M51173 joshua1990 2021-11-15T11:12:37Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DIVIDE-for-just-for-specific-codes/m-p/2190298#M51181 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="92006" data-lia-user-login="joshua1990" class="lia-mention lia-mention-user">joshua1990</a> , Use this for sales </P> <P>sumx(summarize(table, Table[Department], Table[Product], "_1", sum(Table[sales]), "_2", sum(Table[budget])), if(isblank([_2]), Blank(), [_1]))</P> <P>&nbsp;</P> <P>or use this using isinscope or hasonevalue in GT</P> <P>&nbsp;</P> <P><A href="https://xxlbi.com/blog/new-dax-function-isinscope/" target="_blank">https://xxlbi.com/blog/new-dax-function-isinscope/</A></P> Mon, 15 Nov 2021 12:41:28 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DIVIDE-for-just-for-specific-codes/m-p/2190298#M51181 amitchandak 2021-11-15T12:41:28Z