Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

reference

how do i add a dynamic reference line to a bar graph where the reference line refers to targets for each sales person or region and if the filter is used to select that particular sales person or region then the graph should change accordingly. Existing options for reference lines is minimal compared to tableau where I can map the reference lines to a data source containing targets against each category (i.e sales person, region or product line etc etc)
1 ACCEPTED SOLUTION

@Anonymous

 

Please try the following:

 

1. Create a table called Country with unique values of the countrycode

2. Create a table called SalesRep with unique values of the SalesRepCode

3. Create following Relationships

    Pipeline(Country) - Country(Country)

    Pipeline(SalesRep) - SalesRep(SalesRep)

    Target(Country) - Country(Country)

4. Create a Line and Stacked column chart, with Shared Axis as Country

5. Column Values as Pipeline

6. LineValues as TargetValue

Then the output should look like

Capture.GIF

 

7. Create Separate slicers for SalesRep and Country and see the figures changing dynamically.

 

If this solves your issue, please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

Could you use a line and bar graph where the bar numbers come from your Sales and the line numbers come from your goals?

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Yes that is what I was hoping to do. But can't figure out how. Ive been using Tableau for quite a while and its quite simple to connect 2 different database sets - where i have one data set for sales and the other for targets against each region and sales rep. and i can add refernce lines mapping the data set. Trying to do the same in power BI, but unable to figure to how that is done. Would really appreciate if you could let me know the steps to achieve this.

Hi @Anonymous

 

Can you share some data and the output you are exactly wnating to get.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

Hi @CheenuSing, @v-ljerr-msft,

 

Below is screenshot of sample data. I basically would like to reflect the sales target data as a line graph within the bar graph and have it move dynamically as and when the country or sales rep is selected. Its easyily possible in tableau, but im really struggling with this in power bi. Would really appreciate if any of you can assist.

 

 Sample.JPG

 

 

Thanks.

 

 

@Anonymous

 

Please try the following:

 

1. Create a table called Country with unique values of the countrycode

2. Create a table called SalesRep with unique values of the SalesRepCode

3. Create following Relationships

    Pipeline(Country) - Country(Country)

    Pipeline(SalesRep) - SalesRep(SalesRep)

    Target(Country) - Country(Country)

4. Create a Line and Stacked column chart, with Shared Axis as Country

5. Column Values as Pipeline

6. LineValues as TargetValue

Then the output should look like

Capture.GIF

 

7. Create Separate slicers for SalesRep and Country and see the figures changing dynamically.

 

If this solves your issue, please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

Thanks @CheenuSing! That does work .. I had to create another relationship in addition to what you mentioned.

 

Taret (Sales rep) - Sales Rep.

 

For some reason i am unable to set cross directional filter as "Both" for all the realtionships. Any idea why?

Hi @Anonymous,



Ive been using Tableau for quite a while and its quite simple to connect 2 different database sets - where i have one data set for sales and the other for targets against each region and sales rep. and i can add reference lines mapping the data set.

What do you mean about adding reference lines? Are you trying to create relationships between your tables in Power BI Desktop?

 

When you import multiple tables, Power BI allows you to visually set the relationship between these tables or elements. To see a diagrammatic view of your data, use the Relationship view, found on the far left side of the screen next to the Report canvas.

relationship.png

Reference:

How to Manage Your Data Relationships

Create and manage relationships in Power BI Desktop

Guided Learning For Power BI

 

Regards

Anonymous
Not applicable

Hi @v-ljerr-msft,

 

Thanks for your response. By refernce lines I basically mean a combined chart of a bar chart with a line graph - where the columns in the bar chart would represent the sales data of a region or sales rep, and the line would represent the target against that region and sales rep. And both column and line would change accoridngly when selected based on the slicer/filters on the report.

 

 

 

 

I'm unclear of how I could represent the above graph view via "Manage Relationships" option in power BI.

 

 

 

 

Would be great if an example can be shown so I understand better. 

 

Apologies, i'm a beginner to power bi.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.