Forum Discussion

PowerBiLysaker's avatar
PowerBiLysaker
New Member
1 year ago
Solved

Connecting datasets and Displaying correct Values

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

Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    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.

2 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    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.