Combined Actual & Forecast Percentage- Getting Wrong Total

[Fordyce11]

Hello,

I’m looking for some help with a problem I’m stuck on, if anyone can help.  

I need to create a table with efficiency percentages for ‘Actual’, ‘Forecast’, and a combined ‘Actual & Forecast’. So far, I’ve created the below.

This works ok, except for ‘Actuals & Forecast’ Total, which is giving the same as ‘Forecast’ but should be higher. 

Measures used are shown below:

Actual = DIVIDE([Sum Actual],[Sum of Target],0)

Forecast = DIVIDE([Sum of Short Term Forecast],[Sum of Max Potential],0)

Actual & Forecast =

IF (

    MAX ('dim_DATE'[Full Date]) < TODAY(),

    [PE Actual],

    [PE Forecast]

)

Actuals and Forecast values both come from the same table. Unfortunately, I cannot share the data but it looks something like below.

Date	Actual	Target	Max Potential	Short Term Forecast
13/04/2024	900	1200	1650	1050
14/04/2024	1000	1300	1700	1100
15/04/2024		1300	1700	1100

I’ve been trying to find a solution to this, but have so far not found anything describing a problem quite like this.

If anyone can offer any advice or help with this it would be greatly appreciated. 

[Fordyce11]

Hi @v-tianyich-msft,

Thank you for the quick response, I've tried the calculated column as you suggest, but I'm afraid I still can't get it to work as hoped. Results are shown in the table below.

I should have added that the data is for several locations each day, so more like below. Might this change how this should be done?

Date Actual Target Max Potential Short Term Forecast Location
13/04/2024 900 1200 1650 1050 A
13/04/2024 1100 1100 1500 1100 B
13/04/2024 800 900 1050 800 C
14/04/2024 1000 1300 1700 1100 A
14/04/2024 1200 1100 1500 1100 B
14/04/2024 750 900 1050 800 C
15/04/2024 1300 1700 1100 A
15/04/2024 1100 1500 1100 B
15/04/2024 900 1050 800 C

[v-tianyich-msft]

Hi @Fordyce11 ,

It is difficult to assert this, as it can be noticed from the graph that none of the values in the calculated columns are equal to the actual or expected values. Please change your original measure to a calculated column.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[v-tianyich-msft]

Hi @Fordyce11 ,

The problem is with the date selection, which can be solved by using a calculated column:

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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