cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dancruth
Frequent Visitor

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

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.

dancruth_0-1628900032809.png

 

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)

dancruth_1-1628900114903.png

 

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:
dancruth_2-1628900567208.png

 

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!

 

 

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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:

dancruth_0-1628910075902.pngdancruth_1-1628910157561.png

This is the error I got at first:

dancruth_2-1628910234050.png

 

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

dancruth_3-1628910590309.png

 

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!

 

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.