Sales Bucket in Power BI

[SG25048519]

Hello,

I need some help in creating a sales bucket based on company. I have 11,000 companies and have summed up all the sales value based on each company and now want to bucket them under the following bucket.

Company	Sales Value
Dinkar Group	4,950.00
Pareto	3,454.00
Absolute Media	840.00
ABCD Advertising	752.00
Bilbao	1,852.00
Madrid	1,926.00
MUTD	57,426.00
Effervesence	47,984.00
Vodka	26,301.00

For sum I used - 

Sales Value = SUM('Group By Bucket'[Sales])

0-1000

1001-2000

2001-50000

50001-10000

10001-50000

50001+

I am trying to use this column measure:-

SWITCH (

    TRUE (),

    [Sales Value] >= 0

        && [Sales Value] <= 1000, "0-1000",

    [Sales Value] > 1001

        && [Sales Value] <= 2000, "1001-2000",

    [Sales Value] > 2001

            && [Sales Value] <= 50000, "2001-5000",

    [Sales Value] > 5001

            && [Sales Value] <= 10000, "5001-10000",

    [Sales Value] > 10001

            && [Sales Value] <= 50000, "10001-50000",

        [Sales Value] > 50000, "50000+"

However, the result is not really what I want, one company that has bought more than one product is showing up in multiple brackets, which should not be. Since this particular company's sales value is more than 60,000 and should show up only in the 50000+ bucket.

Any help would be greatly appreciated, Kind regards. Thank you.

[serpiva64]

Hi,

please check your formula because there is an error:

SWITCH (

    TRUE (),

    [Sales Value] >= 0

        && [Sales Value] <= 1000, "0-1000",

    [Sales Value] > 1001

        && [Sales Value] <= 2000, "1001-2000",

    [Sales Value] > 2001

            && [Sales Value] <= 50000, "2001-5000",

    [Sales Value] > 5001

            && [Sales Value] <= 10000, "5001-10000",

    [Sales Value] > 10001

            && [Sales Value] <= 50000, "10001-50000",

        [Sales Value] > 50000, "50000+"

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution !

[SG25048519]

Thank you for flagging, I was just giving an example, the original measure is also posted here, which I used.

[amitchandak]

@SG25048519 , Create a measure like

Sales Value =

calculate(SUM('Group By Bucket'[Sales]), allexcept('Group By Bucket'[Company]) )

or

Sales Value =

calculate(SUM('Group By Bucket'[Sales]), filter(allselected('Group By Bucket') , 'Group By Bucket'[Company] = max('Group By Bucket'[Company]) ) )

[SG25048519]

Thank you for help.

I used the second measure, as the first one would get me all the past months hich are filtered in the page level filters.

I used the second one as :- 

Sales Value = CALCULATE(SUM('Group By Bucket'[Sales]),FILTER(ALLSELECTED('Group By Bucket'),'Group By Bucket'[Parent Company] = MAX('Group By Bucket'[Parent Company])))

THEN

Sales Bucket =

SWITCH (

    TRUE (),

    [Group By Bucket ACV] >= 0

        && [Group By Bucket ACV] <= 25000, "0-25000",

    [Group By Bucket ACV] > 25001

        && [Group By Bucket ACV] <= 50000, "25001-50000",

    [Group By Bucket ACV] > 50001

            && [Group By Bucket ACV] <= 100000, "50001-100000",

    [Group By Bucket ACV] > 100001

            && [Group By Bucket ACV] <= 250000, "100001-250000",

    [Group By Bucket ACV] > 250001

            && [Group By Bucket ACV] <= 500000, "250001-500000",

        [Group By Bucket ACV] > 500000, "500001+"

)

However, the desired result is not accurate, some are showing up right, for this one.. Its value is 18000000, so its rightly showing up in bucket 5,00,000+

As some companies are showing up in the wrong bucket..

For this company I select, it should show up in the 0-25,000 bucket as the value of the company is 18898, but it's showing up in 5,00,000+ bucket.

Not sure why it's happening. 

The sales values are all showing up right, however the bucket is not coming correct.

Thank you again and really appreciate for your kind help.

Thank you again.

[v-chenwuz-msft]

Hi @SG25048519 ,

Please try create a calculate column in the 'Group By Bucket' table via 

Sales Value =
CALCULATE (
    SUM ( 'Group By Bucket'[Sales] ),
    FILTER (
        'Group By Bucket',
        'Group By Bucket'[Parent Company]
            = EARLIER ( 'Group By Bucket'[Parent Company] )
    )
)

another column

Sales Bucket =
SWITCH (
    TRUE (),
    [Sales Value] >= 0
        && [Sales Value] <= 25000, "0-25000",
    [Sales Value] > 25001
        && [Sales Value] <= 50000, "25001-50000",
    [Sales Value] > 50001
        && [Sales Value] <= 100000, "50001-100000",
    [Sales Value] > 100001
        && [Sales Value] <= 250000, "100001-250000",
    [Sales Value] > 250001
        && [Sales Value] <= 500000, "250001-500000",
    [Sales Value] > 500000, "500001+"
)

If it does not work, please share your pbix file without sensitive data.

Best Regards

Community Support Team _ chenwu zhu

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

[SG25048519]

Thank you for your help.

The dashboard has monthly data from 2017 onwards, when I use this column measure, it takes up all the months since 2017, even though the page is filtered at page level. Also this is a column and not a measure .

Secondly the bucket query will not accept a column (above), it should be a measure. I tried but dont seem to work. 

Not sure if I can share the file, as all the data is sensitive.

[v-chenwuz-msft]

Hi @SG25048519 ,

Replace sensitive information with false information. It's hard to figure out what the problem is without seeing your model and data.

Best Regards

Community Support Team _ chenwu zhu

[SG25048519 · Accepted solution]

Thank you very much. I will try to use both and update you. Once again, thank you.