Plan and Actual Comparision with different plan versions.

[rahmad]

my data is like following , How can i made comparision of Acutla and Plan [with selected version]

Account Type	Version	Value type	Acutal Amount	Plan Amount	Data
Revenue	1	Actual	1000		01/07/2023
Revenue	AB1	Plan		5000	01/07/2023
Revenue	LF1	Plan		4000	01/07/2023
Revenue	LF2	Plan		3500	01/07/2023
Revenue	LF3	Plan		3200	01/07/2023

[v-kongfanf-msft]

Hi @rahmad ,

I tried the formula you provided and ACTUAL doesn't go away with slicer.

plan_ =
CALCULATE (
    SUM ( 'Table'[Plan Amount] ),
    FILTER ( 'Table', 'Table'[Value type] = "Plan" )
)

actual_ =
CALCULATE (
    SUM ( 'Table'[Acutal Amount] ),
    'Table'[Version] = "1"
        && 'Table'[Value type] = "Actual"
)

Best Regards,
Adamk Kong

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

[rahmad]

It is not working at my end. as i select the plan version , the actual amounts disappeared.

[v-kongfanf-msft]

Hi @rahmad ,

Please provide screenshot information and describe it.

Best Regards,
Adamk Kong

[rahmad]

Following are the screen shots. The first image showing both Actual and Plan columns without any selection of version.

and other image showing only plan version when i select plan version LF1.

[rahmad · Accepted solution]

It is working now, as we create the slicer from the data table instead of Version table.

[rahmad]

I created two mearures Actual and Plan  as follows

Actual = CALCULATE(sum(Data[Actual Current]),Data[Value]="Actual" && Data[Version]="1" )

Plan = CALCULATE(sum(Data[Plan Current]), FILTER(Data, Data[Value]="Plan"))

And add a slicer of Version, when i select any plan version, then actual data disappeared.

[amitchandak]

@rahmad , Create measures like

Calculate(SUm(Table[Acutal Amount]), Table[Value type] ="Actual" && [Version] ="1" ) -Calculate(SUm(Table[Plan Amount]), filter(Table, Table[Value type] ="Budget"))

Create Version slicer and use that.  Rest all filters should apply

[rahmad]

it is not working