Discount Percentage Calculation and Order Qty against Discount Range Visualization

[Gwendolyn]

Hi, 

I have two questions to seek advise on how to set it up in Power BI:

1) I have a set of data as seen in the picture and would like to tabulate the discount percentage instead of having it as a value. Please advise how do i go about doing so. 

2) With this set of similar data, I would like to create a visualization (can be bar or ribbon chart) to illustrate the number of sales orders in the different discount range i.e. 40-45% 12 orders, 45-50% 17 orders etc. Please advise how to do so. 

Thank you!

B REgards, 

Gwendolyn

[v-jialongy-msft · Accepted solution]

Hi @Gwendolyn

Let's address each of your queries step by step.

1.Calculating Discount Percentage

To calculate the discount percentage, you can create a new measure in Power BI. This measure will dynamically calculate the discount percentage based on your sales data. Here's how you can do it:

• Go to the Modeling tab in Power BI Desktop.
  
• Click on New Measure.
  
• Enter the following DAX formula for your new measure:

Discount Percentage = DIVIDE([Discount Amount], [Original Sales Amount])

Replace "[Discount Amount]" and "[Original Sales Amount]" with the actual column names from your dataset that represent the discount amount and the original sales amount before discount, respectively.

2.Visualizing Sales Orders by Discount Range
To visualize the number of sales orders within different discount ranges, you can follow these steps:
Create a new column in your dataset that categorizes each order into a discount range. You can do this by going to the Modeling tab and selecting New Column. Here is an example formula you might use:

Discount Range = 
SWITCH(
    TRUE(),
    [Discount Percentage] >= 0.40 && [Discount Percentage] < 0.45, "40-45%",
    [Discount Percentage] >= 0.45 && [Discount Percentage] < 0.50, "45-50%",
    "Other"
)

Once you have this new column, you can easily create a bar or ribbon chart. Drag the Discount Range column to the Axis field and the Sales Order ID (or equivalent) to the Values field, ensuring to count the number of orders.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.