topic Re: Average versus average based on filter in Custom Visuals Development Discussion https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4626538#M12361 <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="lbendlin_0-1743024159128.png" style="width: 400px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/1254508i78570972AE6ECF0B/image-size/medium?v=v2&amp;px=400" role="button" title="lbendlin_0-1743024159128.png" alt="lbendlin_0-1743024159128.png" /></span></P> <P>&nbsp;</P> Wed, 26 Mar 2025 21:22:58 GMT lbendlin 2025-03-26T21:22:58Z Average versus average based on filter https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4626447#M12360 <P>I need to use the result of a visual matrix to calculate a new column.&nbsp; &nbsp;The data below is a simplified version of the data I have.&nbsp;&nbsp;</P><P>&nbsp;</P><P>Table A</P><TABLE><TBODY><TR><TD>Fuel Type</TD><TD>Gallons</TD><TD>Gross Cost</TD></TR><TR><TD>Diesel Regular</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 126.76</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 417.04</TD></TR><TR><TD>Diesel Premium</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 305.00</TD><TD><P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1,238.30</P></TD></TR><TR><TD>Unleaded Regular</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 508.00</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,452.88</TD></TR><TR><TD>Unleaded Premium</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 653.00</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,148.37</TD></TR><TR><TD>Diesel Regular</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 350.00</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,106.00</TD></TR><TR><TD>Diesel Premium</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 250.00</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 972.50</TD></TR><TR><TD>Unleaded Regular</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 268.00</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 828.12</TD></TR><TR><TD>Unleaded Premium</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 186.00</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 717.96</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Table B</P><TABLE><TBODY><TR><TD>Fuel Type</TD><TD>Preferred Fuel Type</TD></TR><TR><TD>Diesel Regular</TD><TD>Diesel Regular</TD></TR><TR><TD>Diesel Premium</TD><TD>Diesel Regular</TD></TR><TR><TD>Unleaded Regular</TD><TD>Unleaded Regular</TD></TR><TR><TD>Unleaded Premium</TD><TD>Unleaded Regular</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>First desired result is:</P><TABLE><TBODY><TR><TD>Fuel Type</TD><TD>Sum of Gallons</TD><TD>Sum of Gross Cost</TD><TD>Avg Cost/Gal</TD></TR><TR><TD>Diesel Premium</TD><TD>555</TD><TD>2210.8</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.98</TD></TR><TR><TD>Diesel Regular</TD><TD>476.76</TD><TD>1523.0404</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.19</TD></TR><TR><TD>Unleaded Premium</TD><TD>839</TD><TD>2866.33</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.42</TD></TR><TR><TD>Unleaded Regular</TD><TD>776</TD><TD>2281</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.94</TD></TR><TR><TD>&nbsp;Total</TD><TD>2646.76</TD><TD>8881.1704</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.36</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I want it to take the average cost per gallon determined from this visual and add a column that shows what the cost would be if they had purchased the preferred fuel type.</P><P>&nbsp;</P><P>Final Desired Result&nbsp;</P><TABLE><TBODY><TR><TD>Fuel Type</TD><TD>Sum of Gallons</TD><TD>Sum of Gross Cost</TD><TD>Avg Cost/Gal</TD><TD>Cost w/Pref Fuel</TD></TR><TR><TD>Diesel Premium</TD><TD>555</TD><TD>2210.8</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.98</TD><TD>&nbsp;&nbsp;&nbsp; 1,772.98</TD></TR><TR><TD>Diesel Regular</TD><TD>476.76</TD><TD>1523.0404</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.19</TD><TD>&nbsp; &nbsp;1523.04</TD></TR><TR><TD>Unleaded Premium</TD><TD>839</TD><TD>2866.33</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.42</TD><TD>&nbsp;&nbsp;&nbsp; 2,466.18</TD></TR><TR><TD>Unleaded Regular</TD><TD>776</TD><TD>2281</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.94</TD><TD>&nbsp; &nbsp;2281.00</TD></TR><TR><TD>&nbsp;Total</TD><TD>2646.76</TD><TD>8881.1704</TD><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.36</TD><TD>&nbsp;&nbsp;&nbsp; 8,043.21</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>I have tried multiple ways and am not getting it to work.&nbsp; Basically need to show them what it would cost if they didn't purchase the premium fuel.</P> Wed, 26 Mar 2025 20:37:30 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4626447#M12360 HYEasterly 2025-03-26T20:37:30Z Re: Average versus average based on filter https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4626538#M12361 <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="lbendlin_0-1743024159128.png" style="width: 400px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/1254508i78570972AE6ECF0B/image-size/medium?v=v2&amp;px=400" role="button" title="lbendlin_0-1743024159128.png" alt="lbendlin_0-1743024159128.png" /></span></P> <P>&nbsp;</P> Wed, 26 Mar 2025 21:22:58 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4626538#M12361 lbendlin 2025-03-26T21:22:58Z Re: Average versus average based on filter https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4628174#M12364 <P>Thank you! This has gotten me very close.&nbsp; Will you look at my formula and tell me why I am getting inaccurate results?</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="HYEasterly_0-1743104820590.png" style="width: 400px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/1254908i933969A98A025FF3/image-size/medium?v=v2&amp;px=400" role="button" title="HYEasterly_0-1743104820590.png" alt="HYEasterly_0-1743104820590.png" /></span></P><P>If I remove the word SUM on line 4 it gets much closer... but still not correct.</P><P>&nbsp;</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="HYEasterly_1-1743104874151.png" style="width: 400px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/1254909i53E8F87DA6FBA145/image-size/medium?v=v2&amp;px=400" role="button" title="HYEasterly_1-1743104874151.png" alt="HYEasterly_1-1743104874151.png" /></span></P><P>I am sure it is something very simple that I am missing... but I have been looking at it all day and can't see what it is.</P><P>&nbsp;</P><P>Your help is much appreciated!</P> Thu, 27 Mar 2025 19:52:19 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4628174#M12364 HYEasterly 2025-03-27T19:52:19Z Re: Average versus average based on filter https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4628256#M12367 <P>did you rename the [Units]&nbsp; field to "Gallons"&nbsp; for the visual or are these different fields?</P> Thu, 27 Mar 2025 20:48:14 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4628256#M12367 lbendlin 2025-03-27T20:48:14Z Re: Average versus average based on filter https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4628269#M12369 <P>Yes, I renamed it for the visual.</P> Thu, 27 Mar 2025 20:59:24 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4628269#M12369 HYEasterly 2025-03-27T20:59:24Z Re: Average versus average based on filter https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4628301#M12371 <P>no idea. Would be best if you could provide more realistic sample data</P> <P>&nbsp;</P> Thu, 27 Mar 2025 21:41:12 GMT https://community.fabric.microsoft.com/t5/Custom-Visuals-Development/Average-versus-average-based-on-filter/m-p/4628301#M12371 lbendlin 2025-03-27T21:41:12Z