Filter matrix by RankX measure

[Lars_Denmark]

I have a sales table with customers and their revenue and date.

For the X customers with the largest revenue measured over the whole year, I will create a matrix sorted by year, quarter and month, showing the X customers and their revenue in descending order within the month.

I have tried unsuccessfully to create the matrix using the solution in this link to another user's previous question:

Filter "Top N" by a measure 

I use this measure to filter and select the customers with the highest revenue for the year:

My problem is, this measure marks customers with the largest turnover in the month, where I want the customers with the largest turnover for the whole year to be selected.

In the example below, I have chosen to see only the 2 customers with the largest turnover in the year, which are customer I and E, but in the matrix other customers are also shown as their revenue is larger in the month.

In this situation, how to get Power BI to show the matrix only with customer I and E?

I expected to see these amounts for the chosen months (made in Excel):

If I choose to view 3 customers, customer I, E and C should be displayed ...

I would like to use the solution to display a line chart for the selected X customers.

My PBIX file can be downloaded from Wetransfer with this link: https://we.tl/t-yCV0LLNj6H 

Any help greatly apprecaited!

[MFelix]

Hi @Lars_Denmark ,

Try the following code:

Total Revenue Year = CALCULATE(
		[Revenue Measure],
		ALL('DummyData'),
		VALUES('DummyData'[Customer])
	)

Ranking Measure = 
	VAR _ranking = RANKX(
		ALL('DummyData'[Customer]),
		[Total Revenue Year]
	)
	RETURN
		IF(
			[Revenue Measure] <> BLANK() && _ranking <= [Parameter Value],
			_ranking
		)

 Result below and in attach file:

The formula may need some adjustments for the year values since you only have one year of data in the sample but the base is the same.

[Lars_Denmark]

MFelix, many thanks for this solution and it actually gives the result I asked for.

Based on the table, I have now tried to create a corresponding clustered column chart with the filter Ranking Measure <> Blank , which shows the revenue per month for the selected customers, but I don't think I can make it work when I don't include the customers in the chart.

I would therefore like to ask if you have the opportunity to also show how I can get such a line and clustered column chart to show the revenue in columns for, for example, the 3 ranked customers, where Jan = 665, Feb = 2431, Mar = 640

and on the line, I want to show the 3 customers' revenue as a percentage of the month's total revenue for all customers (2nd y-axis)

[MFelix · Accepted solution]

Hi @Lars_Denmark ,

Create the following measures:

Total Revenue for top customers = 
VAR _temptable = ADDCOLUMNS(
		VALUES('DummyData'[Customer]),
		"rank", [Ranking Measure]
	)
	RETURN
		SUMX(
			FILTER(
				'DummyData',
				'DummyData'[Customer] IN SELECTCOLUMNS(
					FILTER(
						_temptable,
						[rank] <> BLANK()
					),
					"d",
					'DummyData'[Customer]
				)
			),
			[Revenue Measure]
		)

% Over Total = DIVIDE([Total Revenue for top customers], [Revenue Measure])

File attach.