Forum Discussion
Helper IStatistical Grouping and Averages in Dataset
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.
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.
Helper IOkay, so I'm nearly there. I applied the Percent Rank to my live data and am close, but I am getting strange results.
Your example seems to be aggregating based on Sales and not Qty Sold, so I attempted to change this:
_Percent Rank =
VAR _vtable =
VAR _vtable = SUMMARIZE(ALLSELECTED('Sales'),'Sales'[Customer Name],"_QTY",SUM('Sales'[Qty Sold]))
RETURN
ADDCOLUMNS(_vtable,"Percent Rank",
-- VAR CurrentQty = [_QTY]
VAR TotalRows = COUNTROWS(_vtable)
VAR _Rank = RANKX(_vtable, [_QTY], , DESC, Dense)
VAR _minvalue = MINX(_vtable,[_QTY])
RETURN IF([_QTY]=_minvalue,0,DIVIDE(TotalRows - _Rank, TotalRows - 1)))
RETURN SUMX(FILTER(_vtable,[_QTY]=SUM('Sales'[Qty Sold])),[Percent Rank])
When filtered for a specific item, it is giving me results like this:
I can't quite figure out why it is giving results above 1 (100%), but it seems to be aggregating some of the results together. When I get rid RANK instead of the PERCENT RANK (by deleting the Divide function and just giving the _Rank result), it is multiplying the rank by the number of times the duplicate Qty Sold is found in the table. For instance, what should be Rank #17 is doubled into Rank #34 because there are two customers with a Qty Sold of 410. There are others where there are 10 Customers with the same Qty Sold and the Rank result is multiplied by 10.
I also tested it in the PBI file you attached by adding another Customer 19 row and adding the same Qty Sold:
How do I make it so duplicate results are evaluated on their own instead multiplying the rank result by the number times the same quantity is found for different customers?