Forum Discussion
Advocate IINeed help in the pricing calculation.
I have a Pricing table and Customer GMV from another table, based on Customer GMV, we have to take the reference row and calculate in that row to get the final amount.
Pricing Table
Ref. | Volume Minimum | Volume Maximum | Price / Floor | BPS |
Row 1 | $0 | $24,999 | $500 | 0.005 |
Row 2 | $25,000 | $249,999 | $500 | 0.005 |
Row 3 | $250,000 | $499,999 | $500 | 0.005 |
Row 4 | $500,000 | $749,999 | $500 | 0.0045 |
Row 5 | $750,000 | $999,999 | $500 | 0.0045 |
Row 6 | $1,000,000 | $1,249,000 | $500 | 0.004 |
Row 7 | $1,250,000 | $10,000,000 | $500 | 0.0035 |
Here I will be taking one example for the whole calculation part
Example: Customer GMV: $300,000
This GMV values lies in 'Row 3'.
Calculations for 'Row 1, 2 and 3':
Considering Max Volume if Amount is beyond that.
Amount 1 = 24999 * (BPS) 0.005 = 124.995
GMV Amount Remainder = 300000 - 24999 = 275001
Amount 2 = 249999 * (BPS) 0.005 = 1249.995
GMV Amount Remainder = 300000 - 275001 = 24999
Amount 3 = 24999 * 0.005 = 124.995
GMV Amount Remainder = 0
Final Amount = Amount 1 + Amount 2 + Amount 3
Final Amount = 124.995 + 1249.995 + 124.995 = 1499.985
For any GMV value I want to calculate the final amount.
Please the suggest the appropriate Dax
I made a sample dataset using the table you provided, I believe the below measure is behaving as you would want it to:
Raw measure code:
GMV Calc =VAR _GMV = 400000 /*Replace with your actual gmv column/measure*/VAR _table = FILTER('Main Data Table', _GMV > 'Main Data Table'[Volume Minimum])RETURNSUMX(_table, (IF(_GMV > [Volume Maximum], [Volume Maximum], _GMV) - (IF([Volume Minimum] = 0, [Volume Minimum], [Volume Minimum] - 1))) * [BPS])
4 Replies
Hi Siddhesh_Pal ,
Please explain the details again, from the formula summarized above, the final expression seems to have nothing to do with [Customer GMV].Example: Customer GMV: $400,000
This GMV values lies in 'Row 3'.Calculations for 'Row 1, 2 and 3':
Considering Max Volume if Amount is beyond that.
Amount 1 = 24999 * (BPS) 0.005 = 124.995
GMV Amount Remainder = 400000 - 24999 = 375001Amount 2 = 249999 * (BPS) 0.005 = 1249.995
GMV Amount Remainder = 400000 - 375001 = 24999
Amount 3 = 24999 * 0.005 = 124.995
GMV Amount Remainder = 0
Final Amount = Amount 1 + Amount 2 + Amount 3
Final Amount = 124.995 + 1249.995 + 124.995 = 1499.985
In the inductive expression, [Customer GMV] has no meaning.
124.995 + 1249.995 + ([Customer GMV]- ([Customer GMV]- 24999 ) ) * 0.005Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Siddhesh_PalHi Anonymous, I apologize for the oversight. There have been slight adjustment in the calculation
please find the updated calculationExample: Customer GMV: $400,000
This GMV values lies in 'Row 3'.Calculations for 'Row 1, 2 and 3':
Considering Max Volume if Amount is beyond that.
Amount 1 = 24999 * (BPS) 0.005 = 124.995
Amount 2 = (249999 - 24999) * (BPS) 0.005 = 1125
Amount 3 = (400000 - 249999) * 0.005 = 750.005
Final Amount = Amount 1 + Amount 2 + Amount 3
Final Amount = 124.995 + 1125 + 750.005 = 2000
Customer GMV is used in Amount 3 calculation
I made a sample dataset using the table you provided, I believe the below measure is behaving as you would want it to:
Raw measure code:
GMV Calc =VAR _GMV = 400000 /*Replace with your actual gmv column/measure*/VAR _table = FILTER('Main Data Table', _GMV > 'Main Data Table'[Volume Minimum])RETURNSUMX(_table, (IF(_GMV > [Volume Maximum], [Volume Maximum], _GMV) - (IF([Volume Minimum] = 0, [Volume Minimum], [Volume Minimum] - 1))) * [BPS])