Coming chart issue

[Anonymous]

Hello experts,

i need help in finding solution or workaround for an issue.

so I have a cluster column line chart where x axis is text type, left Y axis for column is amount in thpusands ranging from 0 to 1500 K, right Y axis for line is % ranging 0 to 100%.

Now,  baseline data came as a new field and as amount in K and it should be added as line in same chart. 
how do I accomplish that? Number for baseline is 900 k. How can I add a flat horizontal line at 900 k?

thanks a lot,

[mwegener]

Hi @Anonymous ,

can you provide sample data and a screenshot?

Maybe a constant line can be used?

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-analytics-pane

[Anonymous]

Thanks. In combo chart, there is nothing in analytics pane.

sample data looks like below:

Date.              Text.               Amt.    %.      Baseline

01012017.   1st of year.       1000.   30.       900

12312016.   End last year.     700.    20.       900

X axis text, y left axis for column amount, y right axis %, tooltip date.

howdo I put baseline date as a horizontal line which is amount data?

thanks

[mwegener · Accepted solution]

Hi @Anonymous ,

maybe this dirty trick will help

PBIX

[Anonymous]

Thanks a lot for your help. So you converted baseline amount to percentage and then placed it. I do not know the back end calculation in data base for creating baseline .So, converting it to percentage based on whole table might be complex. Will think about it.

thanks again.

[v-kelly-msft]

Hi @Anonymous ,

Excellent solution from @mwegener  ,better check it,if there's any question,you can also ask me.I'm also available and willing to solve any problem from you.

Best Regards,
Kelly

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

[mwegener]

Thank you @v-kelly-msft.
Do not be shy and give Kudos. 😉

[Anonymous]

I tried it. Yes, the line is coming around 900 in left Y axis. But it came around 60% in Y right axis in PBI. The graph  I have in Tableau for verification shows the same line passes around 40% in Y right axis. Soon probably I am close . But not there yet.

thanks again

[v-kelly-msft]

Hi @Anonymous ,

How to get the value of around 40%?Can you upload a pic showing the line in Tableau ? Based on the data you showed in the post,the line should be as below:

 
Best Regards,
Kelly

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

[Anonymous]

Hi ,

I could make the value 42% now. Used same idea but only added some filters instead of All. Thanks for the trick.

So in card it is showing 42%. But when I am putting in visual as line, it's not coming as a straight line thru the span of months in X Axis. Instead showing a curvy line with numbers ranging from 36 to 89% for different months. 
how do I get straight line? 

[Anonymous]

Actually I solved my issue, created a column with same Dax and did average, and that worked nicely. Thanks a lot

[Anonymous]

Thanks Kelly,

the data I post only 3 rows from dataset. I have thousands of rows. And as I mentioned, I do not know the backend calculation for baseline. The average amount for baseline is coming as 900.

do you thing Xviz multiple axes chart might work here?

[Anonymous]

Hi @Anonymous ，

I‘ve tried Xviz multiple axes chart,but seems not a good solution for you,see below:

As you said in the last reply,the sample data has only 3 rows from dataset,but there are  thousands of rows actually.So just try the method we suggested,you may get the value you need.

 
Best Regards,
Kelly

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