Filter monthly data based on latest month volumes

[PantherusNZ]

I have a set of data for the last months and mean to display it in a clustered column graph broken down by a particular configuration item as the primary X axis and then clustered by month, so we can see the number of items for that configuration item for each month together.  I then want to limit it to the Top 10 most used config items via filter in order to only focus on the most occuring items.

Here's where it gets tricky, I can do the above no problem IF the filter is based on TOTAL volume of each config item and then we get the 10 biggest users there.  What I ACTUALLY want is to get the top 10 used config items of the latest month only, and then cluster it with the volumes of the 5 months before that.  I do have the last month data and the last 6 months' data in separate tables if that helps.  Is that viable?

[amitchandak]

@PantherusNZ , Assume one of the two is you latest month, current or last

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Then try measure like

TOPN =
VAR temp2 = TOPN(1,all(Table[item]),[MTD Sales],DESC))

RETURN CALCULATE(SUM(Sales[Sales Amount]), temp2 , values(Table[item]))

or

TOPN =
VAR temp2 = TOPN(1,all(Table[item]),[MTD Sales],DESC))

RETURN CALCULATE(SUM(Sales[Sales Amount]), filter(values(Table[item]) ,Table[item] in temp2 ))

[PantherusNZ]

Hi @amitchandak ,

I tried what you suggested but you lost me in the last measure.

Here is the data I'm working with:

I'm trying to sort line items by the Configuration Item column based on their frequency (so I assumed that where you used SUM in your suggestion, that I should actually use COUNT?).  I can get it working fine to find the top 10 Configuration Items based on frequency overall, but what I want is to find the top 10 configuration items used last month as the main selection criteria, and then utilise the clustered columns to compare that frequency to the previous months - so if a configuration item was used heavily last month, but very little in the previous months, it will still appropriately show up accordingly.

That table is the main data store as it has all the tickets in the last 6 months, but I also have the same data but only from last month if that would be useful as a source...