Forum Discussion

ryanjparks's avatar
ryanjparks
Icon for Helper I rankHelper I
1 year ago
Solved

Statistical Grouping and Averages in Dataset

Hey everyone,   I'm curious if there is a way to get statistical averages based on dividing datasets into groups? For instance, I have the following chart as an example:     There are 18 c...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi ryanjparks 

     

    Here's the sample data:

    Table:

    Then add a new measure:

    Percent Rank =
VAR _vtable =
    VAR _vtable =
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            'Table'[Customer Name        .],
            "_SALES", SUM ( 'Table'[Sales           .] )
        )
    RETURN
        ADDCOLUMNS (
            _vtable,
            "Percent Rank",
                VAR CurrentSales = [_SALES]
                VAR TotalRows =
                    COUNTROWS ( _vtable )
                VAR _Rank =
                    RANKX ( _vtable, [_SALES],, DESC, DENSE )
                VAR _minvalue =
                    MINX ( _vtable, [_SALES] )
                RETURN
                    IF ( [_SALES] = _minvalue, 0, DIVIDE ( TotalRows - _Rank, TotalRows - 1 ) )
        )
RETURN
    SUMX (
        FILTER ( _vtable, [_SALES] = SUM ( 'Table'[Sales           .] ) ),
        [Percent Rank]
    )

    Next, add 3 measures:

    Low Volume =
VAR _vtable =
    VAR _vtable =
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            'Table'[Customer Name        .],
            "_SALES", SUM ( 'Table'[Sales           .] ),
            "_AVG", AVERAGE ( 'Table'[$ / ea        .] )
        )
    RETURN
        ADDCOLUMNS (
            _vtable,
            "Percent Rank",
                VAR CurrentSales = [_SALES]
                VAR TotalRows =
                    COUNTROWS ( _vtable )
                VAR _Rank =
                    RANKX ( _vtable, [_SALES],, DESC, DENSE )
                VAR _minvalue =
                    MINX ( _vtable, [_SALES] )
                RETURN
                    IF ( [_SALES] = _minvalue, 0, DIVIDE ( TotalRows - _Rank, TotalRows - 1 ) )
        )
RETURN
    AVERAGEX (
        FILTER ( _vtable, [Percent Rank] >= 0 && [Percent Rank] <= 0.33 ),
        [_AVG]
    )

    Med Volume =
VAR _vtable =
    VAR _vtable =
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            'Table'[Customer Name        .],
            "_SALES", SUM ( 'Table'[Sales           .] ),
            "_AVG", AVERAGE ( 'Table'[$ / ea        .] )
        )
    RETURN
        ADDCOLUMNS (
            _vtable,
            "Percent Rank",
                VAR CurrentSales = [_SALES]
                VAR TotalRows =
                    COUNTROWS ( _vtable )
                VAR _Rank =
                    RANKX ( _vtable, [_SALES],, DESC, DENSE )
                VAR _minvalue =
                    MINX ( _vtable, [_SALES] )
                RETURN
                    IF ( [_SALES] = _minvalue, 0, DIVIDE ( TotalRows - _Rank, TotalRows - 1 ) )
        )
RETURN
    AVERAGEX (
        FILTER ( _vtable, [Percent Rank] >= 0.34 && [Percent Rank] <= 0.66 ),
        [_AVG]
    )

    High Volume =
VAR _vtable =
    VAR _vtable =
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            'Table'[Customer Name        .],
            "_SALES", SUM ( 'Table'[Sales           .] ),
            "_AVG", AVERAGE ( 'Table'[$ / ea        .] )
        )
    RETURN
        ADDCOLUMNS (
            _vtable,
            "Percent Rank",
                VAR CurrentSales = [_SALES]
                VAR TotalRows =
                    COUNTROWS ( _vtable )
                VAR _Rank =
                    RANKX ( _vtable, [_SALES],, DESC, DENSE )
                VAR _minvalue =
                    MINX ( _vtable, [_SALES] )
                RETURN
                    IF ( [_SALES] = _minvalue, 0, DIVIDE ( TotalRows - _Rank, TotalRows - 1 ) )
        )
RETURN
    AVERAGEX (
        FILTER ( _vtable, [Percent Rank] >= 0.67 && [Percent Rank] <= 1 ),
        [_AVG]
    )

    The result is as follow:

     

    Best Regards

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

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

Is the number of buckets fixed? What are the buckets based on?  Exclusively on Sales Value ranges?  Why not on item value ranges? Or profitability?  You mention pricing guidelines based on volume - is that Quantity sold?

 

Sounds like PercentileX may be another option.

ryanjparks's avatar
ryanjparks
Icon for Helper I rankHelper I
1 year ago

The number of buckets are fixed at 3: Low, Middle and High (Qty Sold volume)....however, the size of the buckets (or rather number of customers in each bucket) will vary based on the number of customers who have bought the product in the last year.

 

Item Value (Each) is what I am trying to get the weighted average for each volume bucket. The reason I'm not going for Item Value ranges is because there are customers in all 3 buckets that are at the extreme ranges of the entire price scale, so it adds no value. But if I take the weighted average of each bucket, that gives my sales reps a better idea of where price is relative to annual volume ranges.

 

I will look into PercentileX....that sounds promising, thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ryanjparks 

     

    Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

     

    Best Regards

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