Average with topn

[sadath]

i have following formula to find the average of top 2 sales in each city

AvgTopSales:=AVERAGEX(topn(2,summarize(ALL(Table1),Table1[Parlour],"TotSales",[Tot])),[TotSales])

currently my pivot table look like as below. in city1 i am supposed to get 1646.5 (avg of 1978 &1315)
but above formula returns 1527 for all store/city

Row Labels Tot AvgTopSales
CIty 1
Store1 1978 1527
Store10 1169 1527
Store4 821 1527
Store7 1315 1527
CIty 2
Store11 2013 1527
Store2 1076 1527
Store5 2114 1527
Store8 1028 1527
CIty 3
Store12 715 1527
Store3 1812 1527
Store6 2256 1527
Store9 1459 1527
Grand Total 17756 1527

 

[Ozeroth]

Hi sadath,

Try this instead:

AvgTopSales:=
CALCULATE (
    AVERAGEX (
        TOPN (
            2,
            ADDCOLUMNS ( SUMMARIZE ( Table1, Table1[Parlour] ), "TotSales", [Tot] ),
            [TotSales]
        ),
        [TotSales]
    ),
    ALL ( Table1[Parlour] )
)

The key change is that you need to clear the Parlour filter with ALL ( Table1[Parlour] ) in order to get the top 2 stores within the City (or whatever other filters may be applied).

In your original measure, using ALL ( Table1 ) meant that you were always summarising the entire Table.

I also used ADDCOLUMNS ( SUMMARIZE (...) ) rather than just SUMMARIZE - may not be strictly necessary.

Regards,
Owen

 

[sadath]

Hi,
Thank you very much, that formula works brilliant !!.

one doubt, what is the difference between
ADDCOLUMNS ( SUMMARIZE ( Table1, Table1[Parlour] ), "TotSales", [Tot] ) & SUMMARIZE ( Table1, Table1[Parlour] , "TotSales", [Tot] )

 

[Ozeroth]

You're welcome

The two expressions should both return the same result in this case, but ADDCOLUMNS/SUMMARIZE is generally considered best practice.
See here: https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/