topic Calculate the average of a measure that is returning a percentage variance in Desktop
https://community.powerbi.com/t5/Desktop/Calculate-the-average-of-a-measure-that-is-returning-a/m-p/1801347#M702148
<P>Hi all</P><P> </P><P>Can someone please assist. I have some data over the course of a financial year and I am building a forecasting model. I have created two measures, one tracking variance between actual and budget as $, and one as a %. My two variance measures are: </P><P> </P><DIV><DIV><SPAN>Var Budget vs Actual = IF([Total Units]<>0, [Total Units]-[Budget Units], "")</SPAN></DIV><DIV><DIV><DIV><SPAN>Var % Budget vs Actual = IF([Total Units]<>0,(([Total Units]-[Budget Units])/[Budget Units]), "")</SPAN></DIV></DIV></DIV></DIV><P> </P><P>I want to create another separate measure that gives me the actual average % for the entire FYTD (excluding current month and the months up to end of the financial year), so I can use it outside of the table in other calculations (i.e. as the factor for my forecasting model, rather than using flat, hard coded factors of 5% or 20% growth).</P><P> </P><P>Can anyone assist me with the DAX for this measure, please? Can't seem to get it to work. Below is my table of data, for context. </P><P> </P><DIV><DIV><DIV><DIV> </DIV></DIV></DIV></DIV><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-left" image-alt="PBI snip.PNG" style="width: 700px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/500311i975F630742F3CFEC/image-size/large?v=v2&px=999" role="button" title="PBI snip.PNG" alt="PBI snip.PNG" /></span></P>Thu, 22 Apr 2021 23:11:24 GMTrachel_g2021-04-22T23:11:24ZCalculate the average of a measure that is returning a percentage variance
https://community.powerbi.com/t5/Desktop/Calculate-the-average-of-a-measure-that-is-returning-a/m-p/1801347#M702148
<P>Hi all</P><P> </P><P>Can someone please assist. I have some data over the course of a financial year and I am building a forecasting model. I have created two measures, one tracking variance between actual and budget as $, and one as a %. My two variance measures are: </P><P> </P><DIV><DIV><SPAN>Var Budget vs Actual = IF([Total Units]<>0, [Total Units]-[Budget Units], "")</SPAN></DIV><DIV><DIV><DIV><SPAN>Var % Budget vs Actual = IF([Total Units]<>0,(([Total Units]-[Budget Units])/[Budget Units]), "")</SPAN></DIV></DIV></DIV></DIV><P> </P><P>I want to create another separate measure that gives me the actual average % for the entire FYTD (excluding current month and the months up to end of the financial year), so I can use it outside of the table in other calculations (i.e. as the factor for my forecasting model, rather than using flat, hard coded factors of 5% or 20% growth).</P><P> </P><P>Can anyone assist me with the DAX for this measure, please? Can't seem to get it to work. Below is my table of data, for context. </P><P> </P><DIV><DIV><DIV><DIV> </DIV></DIV></DIV></DIV><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-left" image-alt="PBI snip.PNG" style="width: 700px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/500311i975F630742F3CFEC/image-size/large?v=v2&px=999" role="button" title="PBI snip.PNG" alt="PBI snip.PNG" /></span></P>Thu, 22 Apr 2021 23:11:24 GMThttps://community.powerbi.com/t5/Desktop/Calculate-the-average-of-a-measure-that-is-returning-a/m-p/1801347#M702148rachel_g2021-04-22T23:11:24ZRe: Calculate the average of a measure that is returning a percentage variance
https://community.powerbi.com/t5/Desktop/Calculate-the-average-of-a-measure-that-is-returning-a/m-p/1805377#M703146
<P>Hi <LI-USER uid="101132"></LI-USER> ，</P>
<P>Do you want to <STRONG>forecast </STRONG>the actual value<STRONG> from May 2021 to September 2021</STRONG>? If so, maybe you can refer to the content in the links below to implement it.</P>
<P><A href="https://blog.enterprisedna.co/create-dynamic-forward-forecasts-in-power-bi-w-dax/" target="_self"><STRONG>Creating Forward Forecasts in Power BI Using DAX</STRONG></A></P>
<P><A href="https://www.sqlbi.com/articles/showing-actuals-and-forecasts-in-the-same-chart-with-power-bi/" target="_self"><STRONG>Showing actuals and forecasts in the same chart with Power BI</STRONG></A></P>
<P>Best Regards</P>Mon, 26 Apr 2021 08:15:44 GMThttps://community.powerbi.com/t5/Desktop/Calculate-the-average-of-a-measure-that-is-returning-a/m-p/1805377#M703146yingyinr2021-04-26T08:15:44ZRe: Calculate the average of a measure that is returning a percentage variance
https://community.powerbi.com/t5/Desktop/Calculate-the-average-of-a-measure-that-is-returning-a/m-p/1807176#M703650
<P>I have already built the forecast formula and it works perfectly, but I am using hard coded values in it (i.e. 1.05 for 5%) and I want to use the actual average % variance between actual and budget so that it is a dynamic % that adjusts the forecast. Here is my forecasting formula:</P><P> </P><DIV><DIV><SPAN>Forecast Logs (+5%) = </SPAN></DIV><DIV><SPAN>VAR UnitsLY = CALCULATE([Total Units], DATEADD(DateTable[Date],-1,YEAR))</SPAN></DIV><DIV><SPAN>VAR Units2yrAgo = CALCULATE([Total Units], DATEADD(DateTable[Date], -2, YEAR))</SPAN></DIV><DIV><SPAN>VAR Factor = 1.05</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>DIVIDE([UnitsLY]+[Units2yrsAgo],2,0)*Factor</SPAN></DIV></DIV><P> </P><P>So I just need assistance with the measure itself, to<SPAN> give me the actual average % for the entire FYTD (excluding current month and the months up to end of the financial year)</SPAN></P><P> </P><P><SPAN>If you see the image above, the column Var % Budget vs Actual gives a % variance between the Actual and Budget columns, and if you average it (excluding the variance of the current and future months in the financial year), you get a % value. That is the value I want to be able to grab and use in my forecasting formula as a measure, instead of a hard coded factor of 1.05 (5%) as it shows more accurately what we are likely to finish the year on, above budget. I'm having trouble getting the syntax of this to work in a measure.</SPAN></P>Mon, 26 Apr 2021 22:10:41 GMThttps://community.powerbi.com/t5/Desktop/Calculate-the-average-of-a-measure-that-is-returning-a/m-p/1807176#M703650rachel_g2021-04-26T22:10:41ZRe: Calculate the average of a measure that is returning a percentage variance
https://community.powerbi.com/t5/Desktop/Calculate-the-average-of-a-measure-that-is-returning-a/m-p/1811058#M704676
<P>Hi <LI-USER uid="101132"></LI-USER> ，</P>
<P>What's the calculation logic of var Factor? The below formula is correct or not?</P>
<LI-CODE lang="markup">Factor= average((Budget/Actual)/(Actual-Budget))</LI-CODE>
<P>Best Regards</P>Wed, 28 Apr 2021 09:31:41 GMThttps://community.powerbi.com/t5/Desktop/Calculate-the-average-of-a-measure-that-is-returning-a/m-p/1811058#M704676yingyinr2021-04-28T09:31:41Z