Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
achentrp
Frequent Visitor

Visual for sales by unit price after discount

Hi there

 

I have a table derived from our company's sales order archives for line items that include the following columns:

 

Unit Price

Percent Discount (Expressed as a whole number of 0-100)

Line Amount

 

I would like to generate a visual that shows sales volume by Unit Price after discount. I tried the following measure, but Power BI will not allow me to place the measure into the x-axis on the visual:

 

Unit Price After Discount = SUMX('Sales Order Archives Lines', 'Sales Order Archinves Lines'[Unit_Price]*(1-(DIVIDE('Sales Order Archives Lines'[Line_Discount_Percent],100))))

 

Any suggestions?

5 REPLIES 5
v-lili6-msft
Community Support
Community Support

hi  @achentrp 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual by its row context.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

So you could not drag a measure into x-axis.

 

Second, for your requirement, you could create a column or a bin column like parry2k said,

or create a dynamic segmentation in Power BI

https://powerbi.tips/2016/04/power-bi-histogram-with-bins/

http://tinylizard.com/segmentation-power-bi/

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
pranit828
Community Champion
Community Champion

Hi @achentrp 

 

I believe your main issue is X-axis not the formula. and it is not possible as of now. you can submit a Idea for the same.

 

Currently, measures can be dragged to value or tooltips field, but can’t be dragged to legend or X-axis field in a bar chart.

 

Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

 

Regards,
Pranit





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
BA_Pete
Super User
Super User

Hi @achentrp ,

 

In Power Query, add a new custom column to your Sales Order Archives Lines table like this:

 

= [Unit Price] * ( 1 - ([Line_Discount_Percent / 100) )

 

 

Then change the data type of this column to Text.

Apply changes to the data model and you should then be able to use this new field as a chart axis.

You will still need to create the corresponding measure to populate the values of the visual with.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




amitchandak
Super User
Super User

parry2k
Super User
Super User

@achentrp you should add a column unit price after discount

 

Unit Rate After Disount = 'Sales Order Archinves Lines'[Unit_Price]*(1-(DIVIDE('Sales Order Archives Lines'[Line_Discount_Percent],100))

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.