Forum Discussion

Lars_Denmark's avatar
Lars_Denmark
Frequent Visitor
2 years ago
Solved

Filter matrix by RankX measure

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,...
Need Help
  • MFelix's avatar
    MFelix
    2 years ago

    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.

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
2 years ago

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's avatar
    Lars_Denmark
    Frequent Visitor
    2 years ago

    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's avatar
      MFelix
      Icon for Super User rankSuper User
      2 years ago

      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.