cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PantherusNZ
Helper I
Helper I

Filter monthly data based on latest month volumes

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?

2 REPLIES 2
amitchandak
Super User
Super User

@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 ))

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi @amitchandak ,

 

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

 

Here is the data I'm working with:

PantherusNZ_0-1627533504299.png

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.