Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
user10001
Frequent Visitor

Calculate average for ranks

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.

 

StrataAverage SalesMedian Sales
Top 1-10 States55,00046,000
Top 11-20 States45,00044,000
Top 21-30 States10,00011,000
Top 31-40 States5,0004,000
Other States40003800
1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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 ()
    )

 Calculate average for ranks.gif

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

 

View solution in original post

8 REPLIES 8
v-eqin-msft
Community Support
Community Support

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 ()
    )

 Calculate average for ranks.gif

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

 

@v-eqin-msft That is what I was looking for. Thank you!

v-eqin-msft
Community Support
Community Support

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:

8.26.fo1.gif

 

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

@v-eqin-msft 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.

Screenshot 2020-09-17 095112.png

 

 

v-eqin-msft
Community Support
Community Support

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

8.26.2.1.png

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:

8.26.2.2.png

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

Hi @v-eqin-msft - 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
Memorable Member
Memorable Member

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! 😀

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.