Automatically Highlight Top Values Dependent on Owner

[DJsummers]

Hi - I have looked at many articles and videos and I have nearly solved my problem but not quite. We have a report at the end of each crop (farming business), which shows all issues raised during the cycle:

We would also like to highlight the top 3 most common issues seperately so that the other visuals are highlighted also:

Then the same for 2nd (Lo Temps) and 3rd (CO2) I created a crude table, for now, to select the top 3 and save each as a bookmark:

My top 3 value is a measure ([Sales] is just a count of all the issues) and I have used a filter 'Top 3 is less than 4' to get my top 3

Top 3 = RANKX(ALL('EndOfCropViewForLatestCrop2'[IssueTypeTranslated]), [Sales],,,Dense) 

That all works fine and dandy for the current farm but when I change to a different farm that has a different top 3, Water Consumption is still there but now it has switched to Number 2:

Not a massive problem here but there are instances where we don't have sight of water or feed for certain parameters so wouldn't work.

Is there a way for it to highlight Top 1, Top 2 & Top 3 rather than it using the IssueTypeTranslated as the main sort?

We would like to automate the reports so that we're not having to manually change what the top 3 issues were at the end of each crop cycle.

Any help would be gratefully received. Please let me know if you require more information.
 
@amitchandak, you were a massive help in the past but I'm not demanding you answer!

Dan

[v-rzhou-msft · Accepted solution]

Hi @DJsummers ,

According to your statement, I think you need to create a rank measure as a filter to get dynamic rank based on farm selection.

As far as I know, we couldn't add a measure as a filter in level higher than visual level filter. 

I create a easy sample to have a test.

In your sample, [Sales] measure should be a count. In mine, it is a sum.

Try this code to create a measure.

Rank = 
VAR _SUMMAZIRE =
    SUMMARIZE (
        ALLEXCEPT ( EndOfCropViewForLatestCrop2, EndOfCropViewForLatestCrop2[Farm] ),
        EndOfCropViewForLatestCrop2[IssueTypeTranslated],
        "Sales", [Sales]
    )
VAR _ADDRANK =
    ADDCOLUMNS ( _SUMMAZIRE, "RANK", RANKX ( _SUMMAZIRE, [Sales],, DESC, DENSE ) )
RETURN
    SUMX (
        FILTER (
            _ADDRANK,
            [IssueTypeTranslated] = MAX ( EndOfCropViewForLatestCrop2[IssueTypeTranslated] )
        ),
        [RANK]
    )

Then add this measure into visual level filters in all your visuals and set it to show items less than or equal to 3.

Select Farm A:

Select Farm B:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

Hi DJSummer

I am not sure I have understood.

Sorry, your description goes on a bit 😀

I apprecaite you are keen to show what you can do (which is really good)
but please provide example tables of the source data
and the desired output with a bried description.

Best to copy and paste tables rather than images, then we can quickly import the data and create a solution