Customer Sales Range Analyze

Hi Lily36876 ,

I'd set this up with a number of custom columns in Power Query, then swich between the different scenarios using field parameters.

For example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hce5EQAxCAPAXhQ7OCPkpxaG/ts4TLYbgYkBo5zIEbAaz27z2Vwdr2zRbk816dB6qzZ96UPmDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [saleID = _t, saleValue = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"saleID", Int64.Type}, {"saleValue", Int64.Type}}),

// Relevant steps ----->
    addSale_1k =
        Table.AddColumn(
            chgTypes,
            "sale_1k",
            each Text.Combine(
                {
                    Text.From(Number.RoundDown([saleValue] / 1000, 0) * 1000),
                    Text.From(Number.RoundUp([saleValue] / 1000, 0) * 1000)
                },
                " - "
            ),
            type text
        ),
    addSale_5k =
        Table.AddColumn(
            addSale_1k,
            "sale_5k",
            each Text.Combine(
                {
                    Text.From(Number.RoundDown([saleValue] / 5000, 0) * 5000),
                    Text.From(Number.RoundUp([saleValue] / 5000, 0) * 5000)
                },
                " - "
            ),
            type text
        )
        
in
    addSale_5k

Example output:

You can add as many different value ranges as you like and add them all into a Field Parameter so the end user can select which range type they use.

You just then need to add the parameter field to a table with a measure like this to get what you want:

__noofSales = DISTINCTCOUNT(yourTable[SaleID])

Pete