Sales bucket with bucket values set by slicer

[TJHughes]

Hi,

I think I am having a mental bloke. I have a set of data with lots of information like category (fridge, freezer etc) and items sold and price sold for. I would like to be able to put these in to sales buckets so we can see value of sales for items sold for between X and Y with the values set by a slicer. The idea is that I can have have 3 slicers, one for each bucket to determine the min/max for that bucket. I allows the person to select slicer 1 to min 0 and max 50. Bucket 1 would then only show sales when item was between 0 and 50. While slicer 2 can be set to 50 to 100 and in the table Bucket 2 would only show sales when item was between 50 and 100. They can then compare bucket 1 and 2 to see which value of goods sells best.

Any help is welcome.

[v-achippa]

Hi @TJHughes,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @DataNinja777 and @techies for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue? or let us know if you need any further assistance.
If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @TJHughes,

We wanted to kindly follow up to check if the solution provided by the super user resolved your issue.

If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[DataNinja777]

Hi @TJHughes ,

You can achieve dynamic sales bucketing using slicers by creating disconnected tables that represent the possible minimum and maximum price values for each bucket. For each of your three desired buckets, you need to create two slicer tables—one for the minimum value and one for the maximum. You can generate these using DAX like:

BucketRange = GENERATESERIES(0, 1000, 1)

You can reuse this same table multiple times in slicers for Bucket1Min, Bucket1Max, Bucket2Min, Bucket2Max, and so on. These tables should not be related to your sales data model. Next, create measures to capture the selected values from each slicer using:

Bucket1MinSelected = SELECTEDVALUE(BucketRange[Value])
Bucket1MaxSelected = SELECTEDVALUE(BucketRange[Value])

Repeat for Bucket2 and Bucket3. Then, create a measure that calculates sales within the selected range for each bucket. Assuming your sales data table is called SalesData and contains columns Price and SalesAmount, you can write:

Bucket1 Sales = 
VAR MinVal = [Bucket1MinSelected]
VAR MaxVal = [Bucket1MaxSelected]
RETURN
CALCULATE(
    SUM(SalesData[SalesAmount]),
    SalesData[Price] >= MinVal,
    SalesData[Price] < MaxVal
)

Repeat similar measures for Bucket2 and Bucket3 by swapping in their respective min and max variables. In your table or matrix visual, place the category (like fridge or freezer) on the rows and the three bucket sales measures on the columns. The slicers you’ve added for each bucket will then dynamically control the price ranges included in each bucket, and the table will reflect sales amounts accordingly. This lets users interactively compare which price ranges are generating the most revenue.

Best regards,

[techies]

Hi @TJHughes here is a static method with predefined buckets that works well, your questn is about slicer controlled buckets to select custom min and max ranges, creating what-if parameters could work. Please share a sample dataset to test it dynamically.

Total Sales by Bucket =

VAR SelectedBucket = SELECTEDVALUE(SalesBuckets[Bucket Name])

VAR MinVal = LOOKUPVALUE(SalesBuckets[MinValue], SalesBuckets[Bucket Name], SelectedBucket)

VAR MaxVal = LOOKUPVALUE(SalesBuckets[MaxValue], SalesBuckets[Bucket Name], SelectedBucket)

RETURN

CALCULATE(

    SUMX(

        FILTER(

            SalesData,

            SalesData[SalesPrice] >= MinVal &&

            SalesData[SalesPrice] <= MaxVal

        ),

        SalesData[UnitsSold] * SalesData[SalesPrice]

    )

)