Forum Discussion

Siddhesh_Pal's avatar
Siddhesh_Pal
Icon for Advocate II rankAdvocate II
2 years ago
Solved

Need 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. ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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])
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Siddhesh_Pal
Icon for Advocate II rankAdvocate II
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Siddhesh_Pal
      Icon for Advocate II rankAdvocate II
      2 years ago

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