Show top 3 servers with high utilisation for x months sorted by high utilisation of the latest month

[Pyro1291]

What it might seem to be a simple task in Excel has left me stranded to achieve in Powerbi.

Sort, filter and show by latest month. I have captured by requirement in the below screenshot, please assist

[Pyro1291]

Thank you very much for the response, this works well for showing the latest month alone in the chart.

I wanted to show the past months, but the sort should be based on the utilization of the latest month.

The idea is to see the past month's utilization trend of the server that is in the top 3 (sorted based on the utilization of the latest month)

the chart I have in my original post is what Im desiring to achieve. 

[Anonymous]

@Pyro1291  OK then I think you're going to have to calculate a sum by server in the Query Editor, then sort your data by that column.

[Pyro1291 · Accepted solution]

I have finally figured out the solution, it is quite simple and I'm ashamed that it didn't occur to me earlier.

1. Create a calculated column

LatestMonthEntry =

VAR LatestDate = MAX('Table'[Date])

RETURN

IF(

    MONTH('Table'[Date]) = MONTH(LatestDate) &&

    YEAR('Cluster'[Date]) = YEAR(LatestDate),

    "Yes",

    "No"

)

2. Create another column

UtilizationSort = IF('Table'[LatestMonthEntry]="Yes",'Table'[Utilization)],0)

3. Now in the visual. (Clustered Bar Chart)

In Filter pane - Sort top n servers by 'Sum of UtilizationSort'

And add UtilizationSort in the visual pane tooltip and sort the visual by it.

[Anonymous]

@Pyro1291  Your solution contains some of the recommendations I suggested. It only seems simple now that you've received some guidance on how to solve your problem.

[Pyro1291]

Agree, thank you very much for the guidance!

[Anonymous]

@Pyro1291  You're quite welcome! Kudos would be appreciated. 😊

[Anonymous]

One way you might be able to accomplish this is by taking the following steps:

1. Convert Jan, Feb, Mar to dates (1/1/23, 2/1/23, 3/1/23)

2. Create the following calculated column within your table

Is_Latest Date =

IF ( Sheet1[Month] = MAX ( Sheet1[Month] ), 1, 0 )

I believe this approach will work with the months as you have them formatted though

3. Create a clustered column chart and bring the Server in to the X-axis, Utilization in to the Y-axis, and Month in to the Legend

4. Click on the chart and bring the Is_Latest Date column in to the "Filters on this Visual," and select 1.

5. Click on the Server visual level filter, select the Top N filter type, drag Utilization in to the By value section, and show the Top 3 items