Forum Discussion

user10001's avatar
user10001
Frequent Visitor
6 years ago
Solved

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, an...
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
user10001
Frequent Visitor
6 years ago

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.