topic Comparison of Purchase Price with % Difference in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparison-of-Purchase-Price-with-Difference/m-p/3350603#M125810 <P>Hi all,</P><P>&nbsp;</P><P>I am stuck in a loop and do not know how to get our of it. I have a set of our purchase line items. they are from various years and products. we want to identify on a ArtGroup level the price difference in % per supplier. here is the table i already have:&nbsp;</P><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P>here is the raw data (date, text, number, number, number)</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>product</TD><TD>price</TD><TD>qty</TD><TD>line amount</TD></TR><TR><TD>01.05.2022</TD><TD>012850V1</TD><TD>31</TD><TD>1</TD><TD>31</TD></TR><TR><TD>01.05.2022</TD><TD>012862V2</TD><TD>28,07</TD><TD>3</TD><TD>84,21</TD></TR><TR><TD>01.05.2022</TD><TD>012864V2</TD><TD>1,8</TD><TD>10</TD><TD>18</TD></TR><TR><TD>01.05.2022</TD><TD>012873V1</TD><TD>11,5</TD><TD>10</TD><TD>115</TD></TR><TR><TD>01.05.2022</TD><TD>012877V1</TD><TD>21,5</TD><TD>2</TD><TD>43</TD></TR><TR><TD>01.05.2022</TD><TD>012878V1</TD><TD>56</TD><TD>1</TD><TD>56</TD></TR><TR><TD>01.05.2022</TD><TD>012879V1</TD><TD>16</TD><TD>2</TD><TD>32</TD></TR><TR><TD>01.05.2022</TD><TD>012885V1</TD><TD>197,4</TD><TD>5</TD><TD>987</TD></TR><TR><TD>01.06.2021</TD><TD>012850V1</TD><TD>31</TD><TD>1</TD><TD>31</TD></TR><TR><TD>01.06.2021</TD><TD>012862V2</TD><TD>25,625</TD><TD>8</TD><TD>205</TD></TR><TR><TD>01.06.2021</TD><TD>012864V2</TD><TD>2,252</TD><TD>15</TD><TD>33,78</TD></TR><TR><TD>01.06.2021</TD><TD>012873V1</TD><TD>15,318</TD><TD>10</TD><TD>153,18</TD></TR><TR><TD>01.06.2021</TD><TD>012877V1</TD><TD>21,5</TD><TD>2</TD><TD>43</TD></TR><TR><TD>01.06.2021</TD><TD>012878V1</TD><TD>56</TD><TD>1</TD><TD>56</TD></TR><TR><TD>01.06.2021</TD><TD>012879V1</TD><TD>16</TD><TD>2</TD><TD>32</TD></TR><TR><TD>01.06.2021</TD><TD>012885V1</TD><TD>196,4</TD><TD>5</TD><TD>982</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>we would like to see the price increase per line (that is already working) but we would also see the total price increase per supplier. this would mean for this expamle: the price from 21 would have to be multiplied with the qty for 22 and then the difference in % displayed. overall this would be here -4% on average.</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>product</TD><TD>price</TD><TD>qty</TD><TD>line amount</TD><TD>price 21 x qty 22</TD><TD>diff</TD></TR><TR><TD>01.05.2022</TD><TD>012850V1</TD><TD>31</TD><TD>1</TD><TD>31</TD><TD>31</TD><TD>0%</TD></TR><TR><TD>01.05.2022</TD><TD>012862V2</TD><TD>28,07</TD><TD>3</TD><TD>84,21</TD><TD>76,875</TD><TD>10%</TD></TR><TR><TD>01.05.2022</TD><TD>012864V2</TD><TD>1,8</TD><TD>10</TD><TD>18</TD><TD>22,52</TD><TD>-20%</TD></TR><TR><TD>01.05.2022</TD><TD>012873V1</TD><TD>11,5</TD><TD>10</TD><TD>115</TD><TD>153,18</TD><TD>-25%</TD></TR><TR><TD>01.05.2022</TD><TD>012877V1</TD><TD>21,5</TD><TD>2</TD><TD>43</TD><TD>43</TD><TD>0%</TD></TR><TR><TD>01.05.2022</TD><TD>012878V1</TD><TD>56</TD><TD>1</TD><TD>56</TD><TD>56</TD><TD>0%</TD></TR><TR><TD>01.05.2022</TD><TD>012879V1</TD><TD>16</TD><TD>2</TD><TD>32</TD><TD>32</TD><TD>0%</TD></TR><TR><TD>01.05.2022</TD><TD>012885V1</TD><TD>197,4</TD><TD>5</TD><TD>987</TD><TD>982</TD><TD>1%</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>Average</TD><TD>-4%</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>my results always show something of -26% price increase as it does not respect the 0 values.&nbsp;</P><P>&nbsp;</P><P>thank for your help,</P><P>Andreas</P> Wed, 26 Jul 2023 09:20:08 GMT AndreasSchippan 2023-07-26T09:20:08Z Comparison of Purchase Price with % Difference https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparison-of-Purchase-Price-with-Difference/m-p/3350603#M125810 <P>Hi all,</P><P>&nbsp;</P><P>I am stuck in a loop and do not know how to get our of it. I have a set of our purchase line items. they are from various years and products. we want to identify on a ArtGroup level the price difference in % per supplier. here is the table i already have:&nbsp;</P><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P>here is the raw data (date, text, number, number, number)</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>product</TD><TD>price</TD><TD>qty</TD><TD>line amount</TD></TR><TR><TD>01.05.2022</TD><TD>012850V1</TD><TD>31</TD><TD>1</TD><TD>31</TD></TR><TR><TD>01.05.2022</TD><TD>012862V2</TD><TD>28,07</TD><TD>3</TD><TD>84,21</TD></TR><TR><TD>01.05.2022</TD><TD>012864V2</TD><TD>1,8</TD><TD>10</TD><TD>18</TD></TR><TR><TD>01.05.2022</TD><TD>012873V1</TD><TD>11,5</TD><TD>10</TD><TD>115</TD></TR><TR><TD>01.05.2022</TD><TD>012877V1</TD><TD>21,5</TD><TD>2</TD><TD>43</TD></TR><TR><TD>01.05.2022</TD><TD>012878V1</TD><TD>56</TD><TD>1</TD><TD>56</TD></TR><TR><TD>01.05.2022</TD><TD>012879V1</TD><TD>16</TD><TD>2</TD><TD>32</TD></TR><TR><TD>01.05.2022</TD><TD>012885V1</TD><TD>197,4</TD><TD>5</TD><TD>987</TD></TR><TR><TD>01.06.2021</TD><TD>012850V1</TD><TD>31</TD><TD>1</TD><TD>31</TD></TR><TR><TD>01.06.2021</TD><TD>012862V2</TD><TD>25,625</TD><TD>8</TD><TD>205</TD></TR><TR><TD>01.06.2021</TD><TD>012864V2</TD><TD>2,252</TD><TD>15</TD><TD>33,78</TD></TR><TR><TD>01.06.2021</TD><TD>012873V1</TD><TD>15,318</TD><TD>10</TD><TD>153,18</TD></TR><TR><TD>01.06.2021</TD><TD>012877V1</TD><TD>21,5</TD><TD>2</TD><TD>43</TD></TR><TR><TD>01.06.2021</TD><TD>012878V1</TD><TD>56</TD><TD>1</TD><TD>56</TD></TR><TR><TD>01.06.2021</TD><TD>012879V1</TD><TD>16</TD><TD>2</TD><TD>32</TD></TR><TR><TD>01.06.2021</TD><TD>012885V1</TD><TD>196,4</TD><TD>5</TD><TD>982</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>we would like to see the price increase per line (that is already working) but we would also see the total price increase per supplier. this would mean for this expamle: the price from 21 would have to be multiplied with the qty for 22 and then the difference in % displayed. overall this would be here -4% on average.</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>product</TD><TD>price</TD><TD>qty</TD><TD>line amount</TD><TD>price 21 x qty 22</TD><TD>diff</TD></TR><TR><TD>01.05.2022</TD><TD>012850V1</TD><TD>31</TD><TD>1</TD><TD>31</TD><TD>31</TD><TD>0%</TD></TR><TR><TD>01.05.2022</TD><TD>012862V2</TD><TD>28,07</TD><TD>3</TD><TD>84,21</TD><TD>76,875</TD><TD>10%</TD></TR><TR><TD>01.05.2022</TD><TD>012864V2</TD><TD>1,8</TD><TD>10</TD><TD>18</TD><TD>22,52</TD><TD>-20%</TD></TR><TR><TD>01.05.2022</TD><TD>012873V1</TD><TD>11,5</TD><TD>10</TD><TD>115</TD><TD>153,18</TD><TD>-25%</TD></TR><TR><TD>01.05.2022</TD><TD>012877V1</TD><TD>21,5</TD><TD>2</TD><TD>43</TD><TD>43</TD><TD>0%</TD></TR><TR><TD>01.05.2022</TD><TD>012878V1</TD><TD>56</TD><TD>1</TD><TD>56</TD><TD>56</TD><TD>0%</TD></TR><TR><TD>01.05.2022</TD><TD>012879V1</TD><TD>16</TD><TD>2</TD><TD>32</TD><TD>32</TD><TD>0%</TD></TR><TR><TD>01.05.2022</TD><TD>012885V1</TD><TD>197,4</TD><TD>5</TD><TD>987</TD><TD>982</TD><TD>1%</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>Average</TD><TD>-4%</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>my results always show something of -26% price increase as it does not respect the 0 values.&nbsp;</P><P>&nbsp;</P><P>thank for your help,</P><P>Andreas</P> Wed, 26 Jul 2023 09:20:08 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparison-of-Purchase-Price-with-Difference/m-p/3350603#M125810 AndreasSchippan 2023-07-26T09:20:08Z Re: Comparison of Purchase Price with % Difference https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparison-of-Purchase-Price-with-Difference/m-p/3357013#M126177 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="594435" data-lia-user-login="AndreasSchippan" class="lia-mention lia-mention-user">AndreasSchippan</a>&nbsp;,</P> <P>&nbsp;</P> <P>Here I create a sample based on your requirement, I get result -2.17%.</P> <P><img /></P> <P>You can download my sample file to get more details. If this reply still couldn't help you solve your issue, please share a sample file with me.</P> <P>&nbsp;</P> <P>Best Regards,<BR />Rico Zhou</P> <P>&nbsp;</P> <P>If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.</P> <P>&nbsp;</P> Mon, 31 Jul 2023 01:21:48 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparison-of-Purchase-Price-with-Difference/m-p/3357013#M126177 Anonymous 2023-07-31T01:21:48Z Re: Comparison of Purchase Price with % Difference https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparison-of-Purchase-Price-with-Difference/m-p/3357483#M126193 <P>Thanks Rico, that works perfect.</P> Mon, 31 Jul 2023 08:25:09 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparison-of-Purchase-Price-with-Difference/m-p/3357483#M126193 AndreasSchippan 2023-07-31T08:25:09Z