Forum Discussion
HELP: PROCUREMENT REBATES CALCULATION
Hi power user,
Could you please help me on my powerbi. I'm a newbie user, I'm creating a procurement spend report and I want to show the actual rebates % and actual rebates amount based on a rebates structure. I have 3 tables in my powerbi (1) Append Spend Supplier Details (2) Fiscal Calendar (3) Rebates Structure Table see attached file for table sample and below expected result.
| Fiscal Year Number | Fiscal Period | Business Line | Supplier Parent Name | USD Spend |
| 2021 | 20212101 | Water | Supplier A | 100 |
| 2021 | 20212101 | Transporation | Supplier A | 200 |
| 2021 | 20212101 | Construction Services | Supplier B | 100 |
| 2021 | 20212101 | Transporation | Supplier B | 50 |
| 2022 | 20222102 | Construction Services | Supplier B | 50 |
| 2022 | 20222102 | Water | Supplier A | 400 |
| 2022 | 20222102 | Transporation | Supplier B | 20 |
| 2022 | 20222102 | Construction Services | Supplier A | 10 |
| Suplier Parent Name | Rebates % | Minimum Amount | Maximum Amount |
| Supplier A | 1% | 50 | 199 |
| Supplier A | 2% | 200 | $201 and above |
| Supplier B | 0% | 0 | 100 |
| Supplier B | 1% | 101 | $102 and above |
Expected result are:
Supplier parent name, fiscal year, actual rebates % and actual rebates amount
Thank you
Hi Krissha ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
Method 1: Create measures
Actual rebates % = CALCULATE ( MAX ( 'Rebates Structure'[Rebates %] ), FILTER ( ALL ( 'Rebates Structure' ), 'Rebates Structure'[Minimum Amount] <= MIN ( 'Append Spend Supplier Details'[USD Spend] ) && IF ( IFERROR ( SEARCH ( "and above", 'Rebates Structure'[Maximum Amount] ), 0 ) > 0, 1 = 1, VALUE ( 'Rebates Structure'[Maximum Amount] ) >= MIN ( 'Append Spend Supplier Details'[USD Spend] ) ) ) )Actual rebates amount = SELECTEDVALUE('Append Spend Supplier Details'[USD Spend])*[Actual rebates %]Method 2: Create calculated columns
Column_Actual rebates % = CALCULATE ( MAX ( 'Rebates Structure'[Rebates %] ), FILTER ( ALL ( 'Rebates Structure' ), 'Rebates Structure'[Minimum Amount] <= 'Append Spend Supplier Details'[USD Spend] && IF ( IFERROR ( SEARCH ( "and above", 'Rebates Structure'[Maximum Amount] ), 0 ) > 0, 1 = 1, VALUE ( 'Rebates Structure'[Maximum Amount] ) >= 'Append Spend Supplier Details'[USD Spend] ) ) )Column_Actual rebates amount = 'Append Spend Supplier Details'[USD Spend]*'Append Spend Supplier Details'[Column_Actual rebates %]Best Regards
3 Replies
- MFelix
Super User
Hi Krissha ,
You refer you are adding a sample file however there is no file to download. Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message. Hi Krissha ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
Method 1: Create measures
Actual rebates % = CALCULATE ( MAX ( 'Rebates Structure'[Rebates %] ), FILTER ( ALL ( 'Rebates Structure' ), 'Rebates Structure'[Minimum Amount] <= MIN ( 'Append Spend Supplier Details'[USD Spend] ) && IF ( IFERROR ( SEARCH ( "and above", 'Rebates Structure'[Maximum Amount] ), 0 ) > 0, 1 = 1, VALUE ( 'Rebates Structure'[Maximum Amount] ) >= MIN ( 'Append Spend Supplier Details'[USD Spend] ) ) ) )Actual rebates amount = SELECTEDVALUE('Append Spend Supplier Details'[USD Spend])*[Actual rebates %]Method 2: Create calculated columns
Column_Actual rebates % = CALCULATE ( MAX ( 'Rebates Structure'[Rebates %] ), FILTER ( ALL ( 'Rebates Structure' ), 'Rebates Structure'[Minimum Amount] <= 'Append Spend Supplier Details'[USD Spend] && IF ( IFERROR ( SEARCH ( "and above", 'Rebates Structure'[Maximum Amount] ), 0 ) > 0, 1 = 1, VALUE ( 'Rebates Structure'[Maximum Amount] ) >= 'Append Spend Supplier Details'[USD Spend] ) ) )Column_Actual rebates amount = 'Append Spend Supplier Details'[USD Spend]*'Append Spend Supplier Details'[Column_Actual rebates %]Best Regards
This works for me. super Thank you
