Find the overall discount %

[siva_boggarapu]

Hi Friends,

I need to find overall discount price on below table

Product	Discount%	Sales-After Discount	Before Discount
Table	50.0%	1500	3000
Chair	16.5%	1002	1200
Cup board	3.8%	7693	8000
Pencils	48.8%	5	10
Pens	44.7%	28	50
Eraser	20.5%	4	5
Printer	48.0%	9367	18000
Monitor	36.5%	13335	21000
Kitchen table	21.3%	11800	15000
High stool	73.2%	938	3500
Cabinet	76.2%	1594	6700
Plastic bottle	44.3%	139	250
Copper bottle	55.1%	404	900
Total	?	47810	77615

I need to find Over all Discount price? It should be around 38%. Please let me know what is the formula I need to create?

[stevedark]

Hi @siva_boggarapu 

It should automatically calculate and show the correct total, if you are calculating it in the right way in the rows.

What is important is that you have measures, not dimensions, for all of the number columns - including discount.

The calculation is simply:

1 - (sum([Sales After Discount]) / sum([Sales Before Discount]))

Or, you can refer to the column titles when calculating the discount:

1 - ([Sales-After Discount] / [Before Discount])

In what way is it currently failing for you? Can you share a screenshot?

Steve

https://www.quickintelligence.co.uk/blog/

[BrunPierre]

= Sum([Before Discount]- [Sales-After Discount])/ [Before Discount] * 100

[stevedark · Accepted solution]

Hi @siva_boggarapu 

The first thing you want to do is exclude the total row from the source data, as you are currently double counting in the total row in the source data in the total of the table:

Discount:
LOAD
    Product,
    "Sales-After Discount",
    "Before Discount"
FROM [lib://Discount/PowerBI Sample Data.xlsx]
(ooxml, embedded labels, table is Discount%)
WHERE Product <> 'Total';

Your first column is giving the percentage that the after discount sales is of the total, you can flip that to give you the discount percentage by taking it away from 1. The * 100 is not required, as you can just use the number format of the column to say that it is a percentage:

1 - (sum([Sales-After Discount])/sum([Before Discount]))

The second column is not working as it is currently using the value without aggregation:

Sum([Before Discount]- [Sales-After Discount])/ [Before Discount]

This can be fixed by either using the column name, rather than the field name:

Sum([Before Discount]- [Sales-After Discount])/ [Before]

Or adding an aggregation around the field:

Sum([Before Discount]- [Sales-After Discount])/ sum([Before Discount])

Incidentally, if you just want to use column names (to use the values calculated in the table already) you can simplify to:

(Before - After) / Before

I would suggest your best bet is to use the first expression though, as that can then be used as a Master Item elsewhere, without being in the context of the table.

Hope that makes sense?

Steve