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

     

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.