Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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
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
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 |
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
Proud to be a Datanaut!
@achentrp , only bubble visual can take measure/number on x-axis
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-scatter
@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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |