Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

Create bins and use bin in bar chart

I have created a calculated column:
Category Sales Bin = (IF(Orders[Sale%]>0 && Orders[Sale%]<=0.06, "0% - 6%",
IF(Orders[Sale%]>0.06 && Orders[Sale%]<=0.14,"7% - 14%",
IF(Orders[Sale%]>0.14 && Orders[Sale%]<=0.21,"14% - 21%",
IF(Orders[Sale%]>0.21,"> 21%" )))))

sale% is also a calcualted column which is calculating percentage and is in decimal format.
Now Category Sales Bin is showing "0% - 6%" for all the values
For 13.01 or 12.68 it is showing "0% - 6%".

Why it is showing for all same value .

also i want to use this bin in bar chart and study sale% distribution bin wise

Power Bi File- Link 

 

 

2 ACCEPTED SOLUTIONS

Syndicated - Outbound

Looking at the pbix, I think you already know the answer.  The Sales% in the table visual is an aggregation of the numeric column but the Category Sales Bin 3 is a text field which is not going to aggregate.

Sales% summed actually does make sense in the data but we have to be very careful adding up percentages normally.

 

One solution is to create SUM(sales%) as a measure and create the bucket calculation as a measure which refers to it.  I think you would have to use a variable to store the SUM(sales%) and then use that variable in the rest of the measure.  Don't overcomplicate it.

 

You will need Product SubCategory in the visual to provide the correct context 

 

Good luck.

 

 

View solution in original post

Icey
Community Support
Community Support

Syndicated - Outbound

Hi @Anonymous ,

 

The cause is just like what @HotChilli said.

Please create a Measure like so:

Category Sales Bin =
VAR Sum_Sales =
    SUM ( Orders[Sale%] )
RETURN
     (
        IF (
            Sum_Sales > 0
                && Sum_Sales <= 0.06,
            "0% - 6%",
            IF (
                Sum_Sales > 0.06
                    && Sum_Sales <= 0.14,
                "7% - 14%",
                IF (
                    Sum_Sales > 0.14
                        && Sum_Sales <= 0.21,
                    "14% - 21%",
                    IF ( Sum_Sales > 0.21, "> 21%" )
                )
            )
        )
    )

 

 

Best Regards,

Icey

 

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

View solution in original post

3 REPLIES 3
Icey
Community Support
Community Support

Syndicated - Outbound

Hi @Anonymous ,

 

The cause is just like what @HotChilli said.

Please create a Measure like so:

Category Sales Bin =
VAR Sum_Sales =
    SUM ( Orders[Sale%] )
RETURN
     (
        IF (
            Sum_Sales > 0
                && Sum_Sales <= 0.06,
            "0% - 6%",
            IF (
                Sum_Sales > 0.06
                    && Sum_Sales <= 0.14,
                "7% - 14%",
                IF (
                    Sum_Sales > 0.14
                        && Sum_Sales <= 0.21,
                    "14% - 21%",
                    IF ( Sum_Sales > 0.21, "> 21%" )
                )
            )
        )
    )

 

 

Best Regards,

Icey

 

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

amitchandak
Super User
Super User

Syndicated - Outbound

@Anonymous , Please refer to my Video and the file attached after signature if those can help

https://www.youtube.com/watch?v=CuczXPj0N-k

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

Looking at the pbix, I think you already know the answer.  The Sales% in the table visual is an aggregation of the numeric column but the Category Sales Bin 3 is a text field which is not going to aggregate.

Sales% summed actually does make sense in the data but we have to be very careful adding up percentages normally.

 

One solution is to create SUM(sales%) as a measure and create the bucket calculation as a measure which refers to it.  I think you would have to use a variable to store the SUM(sales%) and then use that variable in the rest of the measure.  Don't overcomplicate it.

 

You will need Product SubCategory in the visual to provide the correct context 

 

Good luck.

 

 

avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)