Connecting datasets and Displaying correct Values

[PowerBiLysaker]

Hello Everyone! 

Long time lurker - first time poster. 

I have a lengthy question so please bear with me. 

I am a self taught Power BI enthusiast and mostly manage until now. 

I have datasets i am bringing in with some KPI values, let's call it DeviationTable, In this table i am using measures currently to sum up my Values for total Volum and total Deviation.

Then i am dividing The Deviation on the Volum to get a percentage to display. 

I have connected up 3 Visuals that have are filtered on the visual itself on a column called ValueChain (1 of 3 values for each of the three visuals) - there are also 2 sclicers that filters all visuals on the page based on Stream and Region. 

So there are 3 criterias to each Visual - each Visual is a bar chart. 

Now the fun part.. i have KPI Goals i want to have as a line across the bars. These values are per now brought in through a KPIGoals table. I ended up creating a reference table for Stream, Region and ValueChain to create relationships (This might have been a mistake?)... These reference tables are used in my slicers and in my Filter - I have connected the KPIGoals table to this with a relationship and i have connected the DeviationTable to these reference tables as well. 

I would like to pull in the Corresponding KPI goal as a value from the KPIGoals table to use as a line across the bar chart and in a Card Visual that follows my 2 slicers and the filter i have in place on my Visuals. 

I am at a loss on what to try - AI is not giving me solutions that work.. 
So i thought of asking here! Hopefully there is something familiar in this. 

Any ideas on how to make this work? 

Thanks beforehand and Regards from a PowerBI Enthusiast

[rohit1991 · Accepted solution]

To overlay KPI goals on your bar chart and ensure they respond to slicers and filters:

1. Simplify the Data Model: Remove unnecessary reference tables if possible. Directly relate DeviationTable and KPIGoals via shared fields like Stream, Region, and ValueChain.

2. Create a KPI Line Measure: KPI_Line =  CALCULATE(
   MAX(KPIGoals[KPI_Value]),
   TREATAS(VALUES(DeviationTable[Stream]), KPIGoals[Stream]),
   TREATAS(VALUES(DeviationTable[Region]), KPIGoals[Region]),
   TREATAS(VALUES(DeviationTable[ValueChain]), KPIGoals[ValueChain]))

3. Add KPI Line to Chart: Add KPI_Line to the Line Values field of your bar chart.

4. Add KPI Line to Card: Use KPI_Line in a card visual to display the goal dynamically.

5. Check Relationships: Ensure relationships between DeviationTable, KPIGoals, and any shared tables are correct (many-to-one). This setup aligns the KPI line and card with slicers and filters.

[Ritaf1983]

Hi @PowerBiLysaker 

Please share sample data (excluding sensitive information) in text format, along with your expected result and any relevant logic. For guidance, refer to

How to provide sample data in the Power BI Forum

If possible, upload a simplified .pbix file using this guide:

 How to upload PBI in Community