CODEWITHSUNDEEP
powerbidax
David Onyango
David Onyango

Reputation: 77

Microsoft Power BI: RANKX AND AGGREGATION OF RESULT

I am having an issues ranking a set of rows then getting the total to use as input to another calculation. I have attempted nested CALCULATE statements and intermediate table using CALCULATETABLE unsuccessfully. Scenario is as follows:

Original table

Item Sales
A     3
B     4
C     2
D     7
E     5

Ranking top N (3)

Item Sales
D     7
E     5
B     4
TOTAL 16

In this example, I am interested in the value 16 for onward processing

Upvotes: 1

Views: 767

Answers (1)

Murray Foxcroft
Murray Foxcroft

Reputation: 13765

Create a CALCULATED COLUMN to rank the sales.

Sale Rank = RANK.EQ(SalesData[Sales], SalesData[Sales])

Create a MEASURE to get the top 3 sales.

Top 3 Sales = CALCULATE(SUM(SalesData[Sales]), SalesData[Sale Rank] <= 3)

enter image description here

Upvotes: 0

Related Questions