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