Calculate average for ranks

[user10001]

Hi I am new to powerBI. I am trying to calculate the average revenue in different strata like below (hypothetical data). I have a sales table for each state. I calculates ranks based on the sales, and I can individually create the measures based on the ranks, but I am not sure how to create a measure/table/column so that I can have a visual like this. Thank you for your help.

Strata	Average Sales	Median Sales
Top 1-10 States	55,000	46,000
Top 11-20 States	45,000	44,000
Top 21-30 States	10,000	11,000
Top 31-40 States	5,000	4,000
Other States	4000	3800

[Anonymous · Accepted solution]

Hi @user10001 ,

Yes. Just change the rank measure like this:

rank =
VAR _rank =
    RANKX (
        FILTER (
            ALL ( Sheet1 ),
            'Sheet1'[Date] >= MIN ( DateSlicer[Date] )
                && 'Sheet1'[Date] <= MAX ( DateSlicer[Date] )
        ),
        CALCULATE ( MAX ( ( 'Sheet1'[Revenue] ) ) ),
        ,
        DESC,
        SKIP
    )
RETURN
    IF (
        MAX ( 'Sheet1'[Date] ) >= MIN ( DateSlicer[Date] )
            && MAX ( 'Sheet1'[Date] ) <= MAX ( DateSlicer[Date] ),
        _rank,
        BLANK ()
    )

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[user10001]

@Anonymous That is what I was looking for. Thank you!

[Anonymous]

Hi @user10001 ,

Sorry to my late reply...

According to my understand, you want to display TOP X based on specific date ,right?

Firstly ,you need to create a date table for slicer ,then just  change the rank measure like this:

rank =
VAR _rank =
    RANKX (
        FILTER ( ALL ( Sheet1 ), 'Sheet1'[Date] = SELECTEDVALUE ( DateSlicer[Date] ) ),
        CALCULATE ( MAX ( ( 'Sheet1'[Revenue] ) ) ),
        ,
        ASC
    )
RETURN
    IF (
        MAX ( 'Sheet1'[Date] ) = SELECTEDVALUE ( DateSlicer[Date] ),
        _rank,
        BLANK ()
    )

 My visualization looks like this:

You could take a look at the pbix file here.

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[user10001]

@Anonymous Thank you. In your pbix file, when I put the date slicer as between two dates, it shows me blank table. Is it possible that I select the date range and it will show the rank like you have shown in the visual? I really appreciate your help in this.

[Anonymous]

Hi @user10001 ,

According to my understanding, you want to calculate the average and median based on rank, right?

You could follow these steps:

1.Enter data to create a new table

2.Use the following formulas:

rank =
RANKX (
    ALL ( RevenueTable ),
    CALCULATE ( MAX ( 'RevenueTable'[Sales] ) ),
    ,
    DESC
)

avg =
SWITCH (
    SELECTEDVALUE ( NewTable[RankTag] ),
    "Top1-10", AVERAGEX ( FILTER ( RevenueTable, [rank] <= 10 ), [Sales] ),
    "Top11-20",
        AVERAGEX ( FILTER ( RevenueTable, [rank] > 10 && [rank] <= 20 ), [Sales] ),
    "Top21-30",
        AVERAGEX ( FILTER ( RevenueTable, [rank] > 20 && [rank] <= 30 ), [Sales] ),
    AVERAGEX ( FILTER ( RevenueTable, [rank] > 30 ), [Sales] )
)

median =
SWITCH (
    SELECTEDVALUE ( NewTable[RankTag] ),
    "Top1-10", MEDIANX ( FILTER ( RevenueTable, [rank] <= 10 ), [Sales] ),
    "Top11-20",
        MEDIANX ( FILTER ( RevenueTable, [rank] > 10 && [rank] <= 20 ), [Sales] ),
    "Top21-30",
        MEDIANX ( FILTER ( RevenueTable, [rank] > 20 && [rank] <= 30 ), [Sales] ),
    AVERAGEX ( FILTER ( RevenueTable, [rank] > 30 ), [Sales] )
)

My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[user10001]

Hi @Anonymous - Appreciate your help. But it still doesn't solve my need. I have the data with individual dates, and I need to have the date slicer to get the average and median revenue for specific ranktag for certain dates. The vissual you have is what I need, I just need to have the date slicer as well. I have attached the sample data I have.

https://drive.google.com/file/d/1c9vXl8LSEHyhRgIbifY_YYXNe9uGISJl/view?usp=sharing 

Thank you for your help.

[lkalawski]

Hi @user10001

Do you have strata values listed in a separate column?
If so, just enter the measure := AVERAGE(Table [Sales]) and :=MEDIAN(Table [Sales]), and Power BI will automatically calculate the average for each group.

If you need any more help, please upload a sample .pbix file and I'll help.

_______________
If I helped, please accept the solution and give kudos! 😀

[user10001]

Hi lkalawski Thank you for your help. I don't have extra column. I created the rank measure based on the sales. Do I need to create one? If so, can you please let me know how I can do that.