Dear All,
I am trying to calculate how much revenue is gained through clients which buy a low annual quantity for specific type of products.
Enclosed I put 2 tables as a reference.
Steps I am trying to do but doesnt work:
Total revenue type good= CALCULATE(SUM(Transaction Table[amount]),FILTER(Transaction Table[type] = "Revenue"), FILTER(Type clients[type] = "Good"))
Total qty sold = CALCULATE(SUM(Transaction Tabel[qty]),FILTER(Type clients[type} = "Good"))
IF(Total qty sold > 5, Total revenue type good,0)
Now I would like to display the amount of revenue generated by "Good" clients while total quantity sold based on item level does not exceed 5 through the year.
Can anyone point me into the right direction?
Transaction Table
| Client name | Date | type | Amount | qty | Itemcode |
| A | 2019 | Revenue | 1000 | 1 | Item 1 |
| B | 2019 | Revenue | 200 | 8 | Item 1 |
| C | 2019 | Revenue | 300 | 8 | Item 1 |
| D | 2019 | Revenue | 500 | 6 | Item 1 |
| E | 2019 | Revenue | 800 | 1 | Item 1 |
| F | 2019 | Revenue | 900 | 2 | Item 1 |
| A | 2019 | Revenue | 500 | 6 | Item 2 |
| B | 2019 | Revenue | 66 | 4 | Item 2 |
| C | 2019 | Revenue | 500 | 2 | Item 2 |
| D | 2019 | Revenue | 300 | 7 | Item 2 |
| E | 2019 | Revenue | 400 | 10 | Item 2 |
| F | 2019 | Revenue | 800 | 1 | Item 2 |
| A | 2019 | Revenue | 500 | 6 | Item 2 |
| B | 2019 | Revenue | 66 | 4 | Item 2 |
| C | 2019 | Revenue | 500 | 2 | Item 2 |
| D | 2019 | Revenue | -300 | 7 | Item 2 |
| E | 2019 | Revenue | -400 | -5 | Item 2 |
| F | 2019 | Revenue | 800 | 1 | Item 2 |
| 1 | 2019 | Costs | -50 | | |
| 2 | 2019 | Costs | -3 | | |
| 3 | 2019 | Costs | -500 | | |
| 4 | 2019 | Costs | -800 | | |
| 5 | 2019 | Costs | -9000 | | |
| 6 | 2019 | Costs | -10000 | | |
| 7 | 2019 | Costs | -5000 | | |
| 8 | 2019 | Costs | -400 | | |
Type client
| Client name | Type |
| A | Good |
| B | Good |
| C | Good |
| D | Good |
| E | Bad |
| F | Bad |
