Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
= Sum([Before Discount]- [Sales-After Discount])/ [Before Discount] * 100
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