Suggestions for filtering based on top results of an aggregation

[FMCgclifford]

Hello,

I have a dataset of transactions that contain a product, value and an owner.  I am grouping into weeks and then showing them at an owner level.  What I would like to do is this

Start with the full set of transactions

Break them into groups by week number (this I can do)

Sum up the values by week (regardless of owner) and retain only the top X products per week

Use this data set as an input to the table that shows the aggregated product/value/owner

If I was just taking the full set of transactions and finding those top X with the highest value I think i could do this with a query-->list and then use the list as a filter on the other dataset.  But with all of the weeks i'm not sure how to proceed.

Is there a way for me to create a visual that does the aggregation (which I can do) then filters for the top X (which I can do) but which then becomes a filter for another visual?

[Anonymous]

Hi @FMCgclifford ,

Not sure if i understand you correctly, but you may use RANKX() function to create a measure to rank the value. Then add this measure to filter to show only top x records.

https://docs.microsoft.com/en-us/dax/rankx-function-dax.

Best Regards,

Jay

[lbendlin]

Forget the visuals, think of it as filters, either scalar or table.  You can create filters either as calculated columns, calculated tables, or measures.  They can be materialized or transient. 

In addition you have your data model.  It supports inner joins and outer joins. The search filter direction can help you to flip that as needed.

You can decide if the data model is sufficient for what you are trying to achieve, or if you need to create a filter measure that you can then use to impact your target visual.

[FMCgclifford]

Definately a bit confused (I'm new to PowerBi)

Can you provide an example of what a "Filter Measure" would be here?

What I would need to do is

Aggregate my data set by Product, Value, Week and then return the top N rows where Value is the highest for each week.

Then use that list of products to filter my main dataset (or the visual for the dataset).  I only need to see one week in the dataset at a time so i'm currently using a visual filter to select the week I am interested in (and pulling in 6 months to the dataset)