Customer Sales Range Analyze

[Lily36876]

Hi Guys

I got a new report develop request,but I have no idea.

Our sales team need to analyze customer's sales range 

But they want to choose price range and per gap by themself

For example:

They key in $1000~$10,000 use $1000 per gap

then count how many sales receip in this gap

so $1000~$10,000 and per gap

I have totolly 3 variable need filter to that user select

[BA_Pete]

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

[Mahesh0016]

@Lily36876 can you please share your End Out Put. Thank You!!