Calculating the average for the top 25% within region, brand and month

[Anonymous]

Hi all, 

I am very new to DAX/PowerBI so any and all help is welcomed. This is also my first post so apologies in advance if I am not able to explain my problem in a clear manner. 

I have a table with 4 columns: Brand, Region, Month and Sales. I want to calculate the average of the top 25% of sales and use it as a benchmark. However, I need to calculate this exclusively within region and brand and during the specific month.

I found this similar post except it is not filtering by multiple categories: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-Average-of-scores-within-top-percen....

Currently, I am creating a column to calculate the rank of each row with RANKX like this: 

P1FR_toprank = RANKX(

ALLEXCEPT(Table,Table[Region],Table[Month],Table[Brand]),

Parity[Sales], ,ASC)

but I get the error: A circular dependency was detected: Parity[P1FR_toprank].</pi>

MY next step is calculating the average for each top25% using something similar to this solution found at https://www.burningsuit.co.uk/find-top-or-bottom-n-percent-dax/ : 

Top 10 Percent =

VAR Rank_To_Find = COUNTROWS ( ALL ( Customers) ) * 0.1

RETURN

CALCULATE ( [_Total Sales] ,

FILTER ( Customers,Customers[Customers Ranked Top]<= Rank_To_Find))

Thanks in advance!

[vojtechsima]

Hi, @Anonymous 
This is a kinda cool question, can you please give me sample data so I can test my hypothesis?
Please share all the tables you will use with the same structure but random numbers (copyable so I can paste it to my Power BI model).
Thanks

[Anonymous]

Here are 50 lines of data (sorry is big but I needed to make sure the combinations were there). I also realized there is one more layer of complexity. I need to not take in considerations the lines that show 0 sales. I am still trying to figure it out an I will post any updated code I generate. 

Thanks!

Region	Brand	Month	Sales
NAM	B	2021/11	81.03
NAM	B	2021/03	94.16
BRA	D	2021/03	100
NAM	B	2021/11	41.03
NAM	B	2021/10	85.56
AND	B	2021/06	88.89
NAM	B	2021/11	67.44
NAM	B	2021/03	94.12
AND	B	2020/12	93.55
NAM	B	2020/07	95.52
NAM	B	2021/05	90.12
NAM	B	2021/02	85
NAM	B	2021/02	86.31
AND	B	2020/07	100
BRA	D	2020/12	82.61
BRA	D	2020/11	100
NAM	B	2020/05	91.09
AND	B	2021/08	100
BRA	B	2020/09	97.62
BRA	D	2021/09	97.44
NAM	B	2021/08	92.51
MEX	B	2021/08	100
NAM	B	2020/06	93.42
NAM	D	2021/12	0
AND	B	2020/10	100
BRA	D	2021/03	100
MEX	B	2021/11	100
BRA	B	2022/03	0
NAM	B	2021/01	94.12
NAM	B	2021/03	91.54
BRA	D	2022/02	0
AND	B	2022/03	0
NAM	B	2021/03	96.97
MEX	B	2021/01	98.51
AND	B	2021/12	28.57
BRA	D	2021/06	100
NAM	B	2022/01	0
BRA	D	2022/03	0
BRA	B	2021/10	88.24
NAM	D	2020/09	0
AND	B	2021/12	66.67
BRA	B	2022/02	0
NAM	B	2020/12	0
NAM	D	2022/03	0
AND	B	2021/08	100
BRA	D	2020/07	0
NAM	B	2020/04	0
MEX	B	2021/12	40
BRA	B	2020/08	76.92
BRA	B	2020/11	0

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

I will get same error when I create a rankd calcualted column by ALLEXCEPT function.

You can try this code to create a rank column.

P1FR_toprank = 
RANKX (
    FILTER (
        'Table',
        'Table'[Region] = EARLIER ( 'Table'[Region] )
            && 'Table'[Brand] = EARLIER ( 'Table'[Brand] )
            && 'Table'[Month] = EARLIER ( 'Table'[Month] )
    ),
    'Table'[Sales],
    ,
    DESC,
    DENSE
)

Then create a measure.

Top 25% Average by Region, Brand and Month =
VAR _CountRow =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Region], 'Table'[Brand], 'Table'[Month] )
    )
VAR _FIND_RANK = _CountRow * 0.25
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[Sales] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Region], 'Table'[Brand], 'Table'[Month] ),
            'Table'[P1FR_toprank] <= _FIND_RANK
        )
    )

Best Regards,
Rico Zhou

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

[Anonymous]

Thanks Rico, it works!! I used the same code to calculate the bottom 25% by using ASC instead of DESC on the rank function.