cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aasheesh1 Member
Member

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

Accepted Solutions
Super User
Super User

Re: reference

@aasheesh1

 

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!
8 REPLIES 8
Super User
Super User

Re: reference

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


aasheesh1 Member
Member

Re: reference

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.

v-ljerr-msft Super Contributor
Super Contributor

Re: reference

Hi @aasheesh1,



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

Super User
Super User

Re: reference

Hi @aasheesh1

 

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!
aasheesh1 Member
Member

Re: reference

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.

aasheesh1 Member
Member

Re: reference

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.

 

 

Super User
Super User

Re: reference

@aasheesh1

 

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!
aasheesh1 Member
Member

Re: reference

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?