reference

[Anonymous]

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)

[v-yulgu-msft]

Hi, @Anonymous

In PowerBI Desktop, there are 6 kinds of built-in reference lines being allowed to add to graph.

As for your scenario, we can work around it via a combinition chart. Fisrt we need a table where lists all the target values of each category.

Create a measure which will be inserted into the graph as line values.

Measure Terget = CALCULATE(SUM(Target_table[Target]))

Best regards,
Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft,

Thanks for your reply.

I noticed you have a taken date as your measure category.

Ive tried the combination chart, but the issue i seem to have is, it works only for one category. Not all. I have 3 different categories that need to be interlinked and cross filtered. 

Sales pipeline by opportunity owner, sub region and product code being measured against sales quota by the same 3 categories - opportunity owner, sub region and product code. 

I have two different data sources - one with sales pipeline data and the other with sales targets by those 3 categories mentioned above. Would you know how to link the data sets in the manage relationship section in power BI? There doesn't seem to be a many to many realtionship option. So ive created separate intermediary tables with unique values for each of the categories. If you refer to the snapshot of the relationship map, you will notice only the sub region is mapped both to the sales pipeline and targets. whereas, the opportunity owner and product lines is only mapped to the sales pipeline, but not the targets.

Any idea how to fix this?