topic Re: Power BI multiple target lines by filtering with slicer + differences on the column in DAX Commands and Tips

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

Evelin_ — Wed, 06 Nov 2024 14:57:31 GMT

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!

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

Sahir_Maharaj — Wed, 06 Nov 2024 22:20:08 GMT

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() )

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

Anonymous — Thu, 07 Nov 2024 08:09:10 GMT

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

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

Evelin_ — Thu, 07 Nov 2024 08:48:35 GMT

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!

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

Evelin_ — Thu, 07 Nov 2024 09:24:39 GMT

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