How to create a column for revenue bands that will aggregate based on slicer view?

[Anonymous]

Hi,

I am trying to create a column that groups revenue into bands, which will then be able to be used as slicers. I initially used this to create these revenue groupings:

each if [Revenue] <= 25000 then "Under 25K" else if [Revenue] >25000 and [Revenue] <50000 then "25K-30K" else if [Revenue] >50000 and [Revenue] <100000 then "50K-100K" else if [Revenue]>100000 and [Revenue] <250000 then "100K-250K" else null)

It worked when looking at each product individually, but when I would look at an aggregate view for customers or month, it would be inaccurate. See below:

How can I create a dynamic column for revenue groups that will automatically aggregate based off of month or customer?

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Create a single slicer table like this:

Create a sum total measure and a control measure, put the control measure in the visual filter and set its value as 1:

Total = 
CALCULATE (
    SUM ( 'Table'[Total Sales] ),
    ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Customer] )
)

Control = 
VAR tab =
    ADDCOLUMNS (
        DISTINCT ( 'Slicer' ),
        "Min",
            SWITCH (
                [Slicer],
                "Under 25K", 0,
                "25K-30K", 25000,
                "50K-100K", 50000,
                "100K-250K", 100000
            ),
        "Max",
            SWITCH (
                [Slicer],
                "Under 25K", 24999,
                "25K-30K", 30000,
                "50K-100K", 100000,
                "100K-250K", 250000
            )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( tab, [Total] >= [Min] && [Total] < [Max] ) ) > 0,
        1,
        0
    )

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

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

[Greg_Deckler]

@Anonymous - That should have done it if you ended up with those values in a column in Power Query. You should just be able to use that column in a slicer and pick the right aggregation, Sum, Count, etc.

Are you saying that you need this dynamically so that you calculate the total with some additional filters and then decide what bucket it is in? You could do that with a disconnected table trick for your slicer In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563

[amitchandak]

@Anonymous , Do you need this on Aggregated values or Row level values. On row level you can use Switch and do.

On Aggregated values, you need to do binning and segmentation. In that case, you need an independent table and then create a formula using that. That table needs to have buckets with limits.

Refer if these example can help

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization