For line and column chart, how to write measure to show STRAIGHT line that's 10% of sum of all sales

[Anonymous]

This is the line and column chart I want to add a straight (constant) 10% of total sales target. The idea being that I want to show KPI metric of one of the business segments (represented by each bar) to see if it has achieved 10% of total sales.

The chart below is similar to what I want it to look like: (I was able to make the below chart by creating a separate Field that references an Excel table and formula to create a constant (straight line) reflecting 10% of COUNT of Projects. But I want to achieve this straight line for Sales Amount (above) in Power BI, not excel. And then eventually apply that DAX formula to this below Count chart as well)

However, when I tried these various measures:

10% of Total Sales = CALCULATE(SUM('CONT-ProjectBillingInvoices'[10% Target]))*.1

OR

10% Target = CALCULATE(SUMX('CONT-ProjectBillingInvoices','CONT-ProjectBillingInvoices'[SalesAmount]))*.1

The result is this:

I thought maybe going in to Format > Y-axis > Un-Show secondary axis might work but it did not.

How can I write his measure so there is a straight line across the chart representing 10% of the sum of all the values in the bar chart?

Thank you!

[mahoneypat · Accepted solution]

FYI that you don't need the CALCULATE in your expressions, unless you are changing the filter context.  That is needed as shown below, but it isn't for simple SUM( ) expressions.  To do that calculation, you need to remove the filter that is coming from the visual (the column used as the categories on your x axis).

10% of Total Sales = CALCULATE(SUM('CONT-ProjectBillingInvoices'[10% Target]), REMOVEFILTERS(Table[ColumnOnXaxis])*.1

Pat

[Anonymous]

Thank you, Pat. After some tweaking, that worked!! (The measures I shared in my post were two of many iterations, and it had devolved into some totally off and I rushed what I copied in...so thank you for pointing some of those out)

For context if you're interested or for those finding this post:

I tried your suggestion with REMOVEFILTERS on my x-axis [Business Area]. I also replaced the '10% Target' with 'SalesAmount' since that was a numeric calcuated column I had created to get 10% of each project Sales amount, so summing those 10% values AND multiplying by .1 is reduncant:

This is the error I got at first:

So I tried removing the *.1 at the end and putting my '10% Target' calculated column back and that worked.

That first bar throws off the aesthetic of the whole thing, but this visual definitely achieves it's purpose now. (No other Busines Area has achieved 10% of Total Sales.)

Thank you, Pat!