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, 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
  • 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

     

8 Replies

  • lkalawski's avatar
    lkalawski
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

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

      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.

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

      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's avatar
    Anonymous
    Not applicable
    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

     

    • user10001's avatar
      user10001
      Frequent Visitor
      5 years ago

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