Dynamic count based on bucket segmented values

[LaxmiNayak]

Hi All,

Am trying to create below attached view in Power Bi which is doable in excel. 
I have two measures count of orders 'Count' and for Revenue as 'Value'. Now I want to club these 'Count' based on the range of revenue 'Value' that I specify.

I need to create a segment (Bucket) values based on condition 

Revenue[Value]>5000

&& Revenue[Value]<=50000, "$5k-$50k",

Revenue[Value]>50001

&& Revenue[Value]<=100000, "$50k-$100k",

and so on.

Here the Revenue[Value] is a measure that I have created.

but how do I arrive at the count 128 having values falling under $50k-$100k.

Basically the table says that 128 count of values will fall under the range $50k-$100k and the total $ value is $75.8M... 1404 is the total count of values which falls under the range $5k-$50k and has the total $ value as $64.4M and so on....

Hope I have explained well and looking forward for your help

Thanks,

Laxmi

[LaxmiNayak]

@v-yiruan-msft, thank you for responding. Unfortunately, we cannot hardcode the count because the numbers keep changing.. 
I will try to take the sample out and upload the PBi file. Thanks for your suggestion.

[v-yiruan-msft]

Hi @LaxmiNayak ,

Thanks for your reply. So it means [Count] will be dynamic,right? Could you please also provide the judgment condition of [Count] later? Thank you.

Best Regards

[LaxmiNayak]

Hi @v-yiruan-msft, I cannot upload the file as am not a super user. 
Also, we cannot have a judgement to group the count of orders that fall under particular segment of revenue say $50k-$100k. 
I need to create a measure that calculates the total orders that has the revenue falling under the segments mentioned in the shared table and just a caveat both the order (count) and Revenue (value) are measures . Hope I have explained you clearly, if not please let me know.  

[v-yiruan-msft]

Hi @LaxmiNayak ,

Since you are not the super user, you can refer the another method refer in the following link to share the file. Please grant me the proper access to it. Thank you.

How to upload PBI in Community

And I still not be clear about your requirement. [Count] is a measure and its value is dynamic. Also there is no special judgement on it, then how can we define it? What's your final expected result? Could you please provide more details to explain it? Later we can check if there is any workaround or other method to achieve it.... Thank you.

Best Regards

[v-yiruan-msft]

Hi @LaxmiNayak ,

Please create a measure as below to get it:

segment (Bucket) =
SWITCH (
    TRUE (),
    [Count] = 2342
        || Revenue[Value] < 5000, "<$5k",
    [Count] = 1404
        || ( Revenue[Value] > 5000
        && Revenue[Value] <= 50000 ), "$5k-$50k",
    [Count] = 128
        || ( Revenue[Value] > 50001
        && Revenue[Value] <= 100000 ), "$50k-$100k",
    [Count] = 178
        || Revenue[Value] > 100000, "$100k+"
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards