User Profile

jpdamd90

Helper I

Joined 2 years ago

16 Posts2 LikesLikes received

View All Badges

User Widgets

Contributions

Re: Create an accurate forecasted at completion measure
Thanks BBF, I've created all those columns and the output of the FAC shows as below. How can i change it so that if the Budget + the variance is higher than the current actual that it takes the Budget + variance as the FAC? Also will the formulas take in to account the element that the variance is associated with? Eg, if Jumpform has 3 levels completed and the average variance of the 3 levels is 6%, and SLAB (NON PT) has 3 levels completed with an average variance of 8% then any uncompleted jumpform elements would show the FAC as budget + 6% where as any SLAB (NON PT) would show as budget + 8%? Hope that makes sense. Thanks
2 years ago Place DAX Commands and Tips
738Views
0likes
1Comment
Create an accurate forecasted at completion measure
Hi, Can anyone help or come up with a good way to work out the most accurate forecast at completion measure. The forecasted at completion currently within the dashboard is just working on IF logic where, if the current actual is greater than the budget then it just uses that as the FAC, if the current is less then it uses the Budget as the FAC. I have a field 'elementcompletedstatus' that shows Yes when the level/element is completed. Could i use the the average variance from the budget for completed elements & levels then for the same elements on uncompleted levels that average gets added to the budget and that becomes the new FAC? Would there be a way to ignore areas where there is no budget or if there is a ridiculous % blow out becasue a small area goes over by a lot? Dashboard is saved in the onedrive link below. thanks so much Power BI
Solved
2 years ago Place DAX Commands and Tips
"DAX "
Forecasting
805Views
0likes
3Comments
Re: SUM & SUMX causing different results, how do I resolve
Finally got it resolved using the below NEWTOTAL AT COMPLETION = IF( ISINSCOPE( DISTINCT_LEVEL_ELEMENT_TABLE[Level] ) , [TESTING FAC 2] , SUMX( ADDCOLUMNS( SUMMARIZE( DISTINCT_LEVEL_ELEMENT_TABLE , DISTINCT_LEVEL_ELEMENT_TABLE[Level] , DISTINCT_LEVEL_ELEMENT_TABLE[Element] ) , "@Totals" , [TESTING FAC 2] ) , [@Totals] ) )
2 years ago Place DAX Commands and Tips
2.6KViews
1like
0Comments
Re: SUM & SUMX causing different results, how do I resolve
Think I'm getting closer to gettin the totals resolved. I've used the below measures which I found from the youtube video linked. I now have the SubTotal for each measure showing correctly but can figure out away to get the Grand Total to calculate correctly. Any ideas? https://www.youtube.com/watch?v=dgwsk2fjWLc TESTING FAC = IF( ISINSCOPE(DISTINCT_LEVEL_ELEMENT_TABLE[Element]), [TESTING FORECASTED AT COMPLETION], SUMX(SUMMARIZE(DISTINCT_LEVEL_ELEMENT_TABLE, DISTINCT_LEVEL_ELEMENT_TABLE[Element],"_Value",[TESTING FORECASTED AT COMPLETION]),[_Value]) ) TESTING LEVEL FAC = IF( ISINSCOPE(DISTINCT_LEVEL_ELEMENT_TABLE[Level]), [TESTING FORECASTED AT COMPLETION], SUMX( SUMMARIZE( DISTINCT_LEVEL_ELEMENT_TABLE, DISTINCT_LEVEL_ELEMENT_TABLE[Level], "_Value", [TESTING FORECASTED AT COMPLETION] ), [_Value] ) )
2 years ago Place DAX Commands and Tips
2.6KViews
0likes
1Comment
Re: SUM & SUMX causing different results, how do I resolve
The totals just don't seem to work correctly at all, see below for Jumpform. Total should be $1,695,659 based on the numbers within that element but it shows as $1,348,848 which is the total of SumbudgetSupplyCost & SumBudgetInsallCost 😕
2 years ago Place DAX Commands and Tips
2.6KViews
0likes
0Comments
Re: SUM & SUMX causing different results, how do I resolve
Thanks BBF, this helped me massively to figure out what was going wrong. Relised that when there was an amount in [Sumbudgetinstall] but no weight in [remaining install] it wouldnt pick up the right figures for the calculation. Added it to what you gave me to make the below and its working now. The only issue with it is that the Total of the column doesnt seem to include the MISC total and $118 that was miscategorised as SLAB (NON-PT) when it shouldnt have had the - in. I've corrected the invoice so it now falls in to SLAB (NON PT) but the total is still out by it and the MISC Element & I've no clue why that would be happening when the column totals them up correctly 😕 Thanks FORECASTEDCOST_INSTALL_BBF = IF( [SumBudgetSUPPLYCOST] > [SumTotalInvoice] && [sumbudgetinstallcost] > [SumTotalInvoice] && [SumBudgetSUPPLYCOST] > [SumClaimCost] && [sumbudgetinstallcost] > [SumClaimCost] && [SumBudgetSUPPLYCOST] > [Scheduled Cost] && [sumbudgetinstallcost] > [Scheduled Cost] && [SumBudgetsupplycost] + [SumbudgetInstallCost] > [SumTotalInvoice] + [SumClaimCost] + [Scheduled Cost], [SumBudgetsupplycost] + [SumbudgetInstallCost], IF( [SumTotalInvoice] + [SumClaimCost] + [Scheduled Cost] > ([SumBudgetsupplycost] + [SumbudgetInstallCost]), [SumTotalInvoice] + [SumClaimCost] + [Scheduled Cost] + [REMAINING SUPPLY] * 1800 + IF( ISBLANK([REMAINING INSTALL]) || [REMAINING INSTALL] = 0, [sumbudgetinstallcost], [REMAINING INSTALL] * 1200 ), [SumBudgetsupplycost] + [SumbudgetInstallCost] ) )
2 years ago Place DAX Commands and Tips
178Views
0likes
0Comments
Re: SUM & SUMX causing different results, how do I resolve
Apologies, see below with fields included. If Supply Budget [sumbudgetsupplycost] and Install Budget [sumbudgetinstallcost] are higher than actual supply [SumTotalInvoice], actual install [SumClaimCost] and sch forecast [Scheduled Cost] then the forecasted at completion would just be the budgets combined ([SumBudgetsupplycost] + [SumbudgetInstallCost]). If the actual supply [SumTotalInvoice], actual install [SumClaimCost] and sch forecast [Scheduled Cost] combined are already higher than the budget ([SumBudgetsupplycost] + [SumbudgetInstallCost]), the element has gone over budget and the forecasted at completion will be the [SumTotalInvoice], actual install [SumClaimCost] and sch forecast [Scheduled Cost] + Supply remaining (T)[Remaining Supply] * 1800 + Install Remaining[Remaining Install] * 1200
2 years ago Place DAX Commands and Tips
2.7KViews
0likes
4Comments
Re: SUM & SUMX causing different results, how do I resolve
Sorry I didn't explain the logic behind it very well. So its trying to get a forecasted at completion cost for the level/element. If Supply Budget and Install Budget are higher than actual supply, actual install and sch forecast then the forecasted at completion would just be the budgets combined. If the supply/install/sch combined are already higher than the budget, the element has gone over budget and the forecasted at completion will be the Total of Supply $ + Install $ + Sch $ + Supply remaining (T) * 1800 + Install Remaining * 1200 IIf possible then for completed elements (Ones that have YES in the Complete column. Calculate the average variance from the budget and have this applied to the same element budget for later levels. That way the forecasted at completion would be If the Budget supply + the budget install + the Average % overrun from previous levels for that element is greater than the actual supply + install + sch cost then it would display as the budget with that % added on. Hope this makes sense. thanks again
2 years ago Place DAX Commands and Tips
2.7KViews
0likes
6Comments
Re: SUM & SUMX causing different results, how do I resolve
Still some issues when I look at the other elements mate sorry. See below. Your total shows as $110,866 where the calc should be Actual Supply $ 62735 + actual install $ 70776 + sch cost $27338 + Remaining install (3.64 * 1200 = $4368) = $165,217 😞
2 years ago Place DAX Commands and Tips
2.8KViews
0likes
8Comments
Re: SUM & SUMX causing different results, how do I resolve
Thanks BBF, It still doesn't seem to be returning the correct value for the other levels further down the list. It looks to only return the budget Install Cost not the combined budget install and budget supply?
2 years ago Place DAX Commands and Tips
2.8KViews
0likes
10Comments

Data Privacy

Microsoft Fabric Community and Privacy

To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.