Forum Discussion

DJsummers's avatar
DJsummers
Helper I
4 years ago
Solved

Automatically Highlight Top Values Dependent on Owner

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

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

2 Replies

  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.