Need help in the pricing calculation.

[Siddhesh_Pal]

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

[Anonymous]

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 = 375001 

Amount 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.005

Best Regards,
Gao
Community Support Team

If 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_Pal]

Hi @Anonymous, I apologize for the oversight. There have been slight adjustment in the calculation

please find the updated calculation

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

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

[Anonymous · Accepted solution]

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])

RETURN

SUMX(_table, (IF(_GMV > [Volume Maximum], [Volume Maximum], _GMV) - (IF([Volume Minimum] = 0, [Volume Minimum], [Volume Minimum] - 1))) * [BPS])

[Siddhesh_Pal]

Hi @Anonymous, Thanks for your help.
The solution is working perfectly for me.