Forum Discussion

Lily36876's avatar
Lily36876
Helper II
3 years ago

Customer Sales Range Analyze

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

 

Need Help

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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