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

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.

Statistical Grouping and Averages in Dataset.pbix37 KB

ryanjparks

Helper I

1 year ago

Okay, I could not figure it out after spending too much time on this. So here goes with details that are in a usable format:

Below is a table with sales for two items: ItemID DR1001 & ZA5009 (made up items). Every row represents a sale made to a customer for a specific item. There are many rows to a single invoice in this table

Customer Name .	ItemID .	Sales .	Qty Sold .	$ / ea .
Customer 1	DR1001	$54,990	1833	$30
Customer 10	DR1001	$35,000	1000	$35
Customer 11	DR1001	$33,985	971	$35
Customer 15	DR1001	$26,000	650	$40
Customer 12	DR1001	$33,005	943	$35
Customer 10	DR1001	$34,055	973	$35
Customer 15	DR1001	$19,000	475	$40
Customer 16	DR1001	$14,985	333	$45
Customer 4	DR1001	$43,989	1419	$31
Customer 7	DR1001	$39,996	1212	$33
Customer 16	DR1001	$9,000	200	$45
Customer 17	DR1001	$9,990	222	$45
Customer 18	ZA5009	$10,980	244	$45
Customer 6	DR1001	$37,014	1194	$31
Customer 13	DR1001	$21,000	525	$40
Customer 14	DR1001	$22,000	550	$40
Customer 2	DR1001	$54,000	1800	$30
Customer 9	ZA5009	$33,990	1030	$33
Customer 2	DR1001	$51,000	1700	$30
Customer 3	DR1001	$51,990	1733	$30
Customer 8	DR1001	$39,006	1182	$33
Customer 11	DR1001	$33,985	971	$35
Customer 17	DR1001	$13,995	311	$45
Customer 3	DR1001	$50,010	1667	$30
Customer 18	ZA5009	$9,990	222	$45
Customer 4	DR1001	$41,974	1354	$31
Customer 5	ZA5009	$41,013	1323	$31
Customer 13	DR1001	$28,000	700	$40
Customer 14	DR1001	$20,000	500	$40
Customer 1	DR1001	$51,990	1733	$30
Customer 5	ZA5009	$38,006	1226	$31
Customer 12	DR1001	$31,010	886	$35
Customer 6	DR1001	$42,005	1355	$31
Customer 7	DR1001	$34,980	1060	$33
Customer 8	DR1001	$36,003	1091	$33
Customer 9	ZA5009	$38,016	1152	$33

I need to be able to use a Slicer to filter for the desired ItemID.....Easy enough, so let's say I create a slicer and select ItemID "DR1001". I need the data to be displayed like this in the table output on the page:

Customer Name .	Sum of Sales .	Sum of Qty Sold .	Avg of $ / ea .	Percent Rank .
Customer 1	$106,980	3,566	$30.00	100.00%
Customer 2	$105,000	3,500	$30.00	92.80%
Customer 3	$102,000	3,400	$30.00	85.70%
Customer 4	$85,963	2,773	$31.00	78.50%
Customer 6	$79,019	2,549	$31.00	71.40%
Customer 7	$74,976	2,272	$33.00	57.10%
Customer 8	$75,009	2,273	$33.00	64.20%
Customer 10	$69,055	1,973	$35.00	50.00%
Customer 11	$67,970	1,942	$35.00	42.80%
Customer 12	$64,015	1,829	$35.00	35.70%
Customer 13	$49,000	1,225	$40.00	28.50%
Customer 14	$42,000	1,050	$40.00	14.20%
Customer 15	$45,000	1,125	$40.00	21.40%
Customer 16	$23,985	533	$45.00	0.00%
Customer 17	$23,985	533	$45.00	0.00%

Percent Rank is what I'm looking for, based on totals grouped by Customer Name. It needs to be calculated just like Excel does, where I can then create 3 separate Measures that will Group the resulting table data into 3 categories: Low volume (0-33%), Medium volume (34-66%) and High Volume (67-100%). I will then get a Weighted Average sale Price Each for each grouping, which would calculate the Sum of Total Sales / Sum of Qty Sold. I'd then place those measures in Cards which would display the Low, Medium and High volume average sales price each for the sales reps to see, as a guideline to volume based pricing. It would look like this:

So far, no matter what I do I cannot get a percent rank per Customer Name based on the Quantity Sold against all the other customers. I believe the reason for this is because I am using summed data and not single, individual rows (but I am not sure about this). When I have done a COUNTROWS on the data, it is counting the number of rows found in the source data table, not the number of rows resulting in the output table. And further, I could not get a proper comparison in the total Qty Sold for a given customer against a total Qty Sold for a different customer in the output table.

Thanks for any help.

Forum Discussion

Statistical Grouping and Averages in Dataset

Related content

Activity Statistics

Grouped Average

average by group

Average by group

Fabric & Azure Data User Group Program Requirements & Benefits