Budget fulfilment YTD on Totals

[Anonymous]

Hi Power BI experts 🙂

I'm struggling with one calculation in DAX. I want to calculate YTD budget fulfilment and it's working fine until I collapse the numbers to a year level. Please take a look at the pictures below.

This is how it looks with the split by month. The YTD fulfilment is calculated correctly:

When the data are aggregated to year level, then the YTD score is also correct but not the one that I want to show in the report:

Is there any chance to show 99,88% when the data are aggregated?

The formulas I have used:

Budget CY kUER:=CALCULATE(SUMX('Sales Forecast'; IF([Measure Type] = "Budget"; [Amount EUR];0)); DATESYTD('Date'[DateValue]))

Invoiced Sales Ref CY kEUR:=CALCULATE(SUMX('Sales Forecast'; IF([Measure Type] = "Actual Sales"; [Amount EUR];0)); DATESYTD('Date'[DateValue]))

Budget fulfilment YTD (%) by EUR amount:=IF('Sales Forecast'[Invoiced Sales Ref kEUR] = 0; BLANK(); DIVIDE('Sales Forecast'[Invoiced Sales Ref CY kEUR]; 'Sales Forecast'[Budget CY kUER]))

Thank you for any suggestions and help.

[amitchandak]

Try like

Budget CY kUER:=CALCULATE(SUMX('Sales Forecast'; [Amount EUR]); DATESYTD('Date'[DateValue]);[Measure Type] = "Budget")

 

Invoiced Sales Ref CY kEUR:=CALCULATE(SUMX('Sales Forecast'; [Amount EUR]); DATESYTD('Date'[DateValue]);[Measure Type] = "Actual Sales")

 

Budget fulfilment YTD (%) by EUR amount:=IF('Sales Forecast'[Invoiced Sales Ref kEUR] = 0; BLANK(); DIVIDE('Sales Forecast'[Invoiced Sales Ref CY kEUR]; 'Sales Forecast'[Budget CY kUER]))

Appreciate your Kudos.

[Anonymous]

@amitchandak 

Thank you for the answer, but the result is the same:

[V-lianl-msft]

Hi @Anonymous ,

Did you create the calculated column?

Please try to create a measure instead of the calculated column.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@V-lianl-msft: Actually this is a measure built in Data Tools.

[V-lianl-msft]

Hi @Anonymous ,

If the problem persists,could you please share sample pbix?(Please mask any sensitive data before uploading)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

@Anonymous 

Any chance you can share a PBIX file with dummy data? (using OneDrive, Google Dirve, Dropbox or whatever cloud service)