Hello,
I am running a report in Power Bi (SQL data) where I have hunders of companies. The goal is to track if the discount is properly applied in the system. The discount applies 5% for 2 or more contracts, and 10% for more than 4 contracts.
And life would be great if there were no exceptions. Two issues:
1) first one is that there are exceptions for the fleet discount eligibility. Lets say customer VIP is eligible for 5% even if they have 1 contract active and there wont be more contracts signed.
I have managed to overcome it with the formula:
if [Company ID]="454674" and [Number of contracts per Company name]=1 then "5%" else if [Number of contracts per Company name]>=4 then "10%" else if [Number of contracts per Company name]>=2 then "5%" else "0.00")
2) The second issue is that each company has individual ID, but we should sum up the number for contract for each divisions (Clothes ABC Mexico+Clothes ABC Texas; Shirts LLC+Shirts LLC Montana).
I have added the table below and in red you can see the desired column result. I would greatly appreciate your support.
| Company ID | Company name | Contract ID | Number of contracts per Company name | Discount for 2 and more contracts (5%) for more than 4 (10%) |
| 101102 | Clothes ABC Mexico | 44-44 | 1 | 5% |
| 103103 | Clothes ABC Texas | 99-99 | 1 | 5% |
| 103111 | Wool ABC | 88-88 | 1 | 0% |
| 104123 | Shirts LLC | 77-78 | 4 | 10% |
| 104123 | Shirts LLC | 77-77 | 4 | 10% |
| 104123 | Shirts LLC | 77-79 | 4 | 10% |
| 104123 | Shirts LLC | 77-71 | 4 | 10% |
| 121545 | Shirts LLC Montana | 56-87 | 2 | 10% |
| 121545 | Shirts LLC Montana | 56-89 | 2 | 10% |
| 121323 | Belts | 23-91 | 2 | 5% |
| 121323 | Belts | 23-92 | 2 | 5% |
| 454674 | VIP | 33-87 | 1 | 5% |
