Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
LaxmiNayak
Frequent Visitor

Dynamic count based on bucket segmented values

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.

LaxmiNayak_0-1669730524184.png

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

5 REPLIES 5
LaxmiNayak
Frequent Visitor

@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.

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.  

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.