Power BI multiple target lines by filtering with slicer + differences on the column

[Evelin_]

Hello,

I would like to create a chart where actuals are columns/months and target can be selected with slicer and it changes dynamically. Even more if possible, I would like to add the %diff on top of the columns vs the selected target. I did the chart against 1 target, but I can not develop it further with more targets. I show you 1 column example where target is the grey short line and the figures are calculated with dax.

 

Is it possible to have 3 different targets in 1 chart and after selecting one by slicer, the differences will appear? I dont want to have 3 targets on the chart at the same time.

Maybe my table does it a little bit harder, as I have 1 database where there is 1 column with the version, meaning actual, BP, PY, everything is in 1 column. See a dummy version:

Thank you in advance for the help!

[Evelin_]

I did it, Thank you for your help! it was very useful to finish my chart 🙂

[Evelin_]

Hello Sahir, 

Thank you for the dax, with your help I created this measure succesfully in my PowerBI. Only question left is how I add it on the chart and connect it with the slicer? So far I had the posting period in X axis, actual sales column y axis, and 1 target line on line y axis, and this is what I want to change based on slicer + the value label.

Thank you very much!

[Anonymous]

Hi @Evelin_ ,

Did Sahir_Maharaj reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[Sahir_Maharaj · Accepted solution]

Hello @Evelin_,

Can you please try the following:

Actual_vs_Target_Display = 
VAR SelectedTarget = SELECTEDVALUE('Table'[Version]) -- Get the selected target from the slicer
VAR ActualSales = 
    CALCULATE(
        SUM('Table'[Sales]),
        'Table'[Version] = "ACT"
    )
VAR TargetSales = 
    CALCULATE(
        SUM('Table'[Sales]),
        'Table'[Version] = SelectedTarget
    )
VAR SalesDifference = ActualSales - TargetSales
VAR PercentDifference = 
    IF(
        TargetSales <> 0,
        DIVIDE(SalesDifference, TargetSales, 0),
        BLANK()
    )
RETURN
    IF(
        NOT(ISBLANK(ActualSales)),
        ActualSales & " (Target: " & TargetSales & ", Diff: " & FORMAT(PercentDifference, "0.00%") & ")",
        BLANK()
    )