Calculate total price based on three conditions

[Anonymous]

Hi,

I would like to make a calculation for finding the price based on amount of people.

If count ParticipantID is 0-2500 then 3 USD

and count of ParticipantID 2500-20000 then 4 USD

and count of ParticipantID above 20000 then 5 USD

I have a ParticipntID-field where I can make a count and then the prices are like stated above.

Can someone help me making a measure calculating the total price based on the amount of participants?

Thank you!

Examples below

1st example:

25000 participants then the price would be:

2500 * 3 USD = 7500 USD

17500 * 4 USD = 70000 USD

5000 * 5 USD = 25000 USD

total =  7500 + 70000 + 25000 = 102500 USD

2nd example:

5000 participants then the price would be:

2500 * 3 USD = 7500 USD

2500 * 4 USD = 10000 USD

Total = 7500 + 10000 = 17500 USD

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Parameter – 1 to 25000

Here are the steps you can follow:

1. Create calculated column.

price =
SWITCH(
    TRUE(),
    'Table'[Parameter]>=0&&'Table'[Parameter]<=2500,3,
    'Table'[Parameter]>0&&'Table'[Parameter]<=20000,4,
    'Table'[Parameter]>20000,5)

amount of participants =
var _2500=
COUNTX(FILTER(ALL('Table'),'Table'[Parameter]>=0&&'Table'[Parameter]<=2500),[Parameter]) * CALCULATE(MAX('Table'[price]),FILTER(ALL('Table'),'Table'[Parameter]>=0&&'Table'[Parameter]<=2500))

var _20000=
COUNTX(FILTER(ALL('Table'),'Table'[Parameter]>2500&&'Table'[Parameter]<=20000),[Parameter]) * CALCULATE(MAX('Table'[price]),FILTER(ALL('Table'),'Table'[Parameter]>2500&&'Table'[Parameter]<20000))

var _more200000=
COUNTX(FILTER(ALL('Table'),'Table'[Parameter]>20000),[Parameter]) * CALCULATE(MAX('Table'[price]),FILTER(ALL('Table'),'Table'[Parameter]>20000))

return
_2500+_20000+_more200000

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Thank you very much! That worked 🙂

[amitchandak]

@Anonymous , if the count of participants is a measure then you need to try binning/ bucketing 

refer if this can help

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[Anonymous]

Hi amitchandak,

Thank you for your response.

I have seen your link but I don't think that fits here as well? I am not looking for a bucketing but instead a calculation for the price based on the amount of three buckets. I might be wrong though?

In case you believe the bucket can be used for the calculation your help would be much appreciated.