cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Top & Bottom M of the Top N by Parametric Metrics

Hi,

given a Contoso-like dataset, I am trying to create a dashboard where there should be two main visualizations:

1) Top 100 SalesKeys by Sales

2) Top & Bottom 5 SalesKeys by Profit, where this second Top/Bottom is computed on the previous list of SalesKeys

The visualizations can be filtered and, in a second step the metrics which lead the ranking should be parametric as well as the number of records per each viz (top/bottom M of the top N).

As a first step I created the following matrixes

Using the following DAX:

Top 100 by Sales =
var rankSales =
RANKX(
ALLSELECTED(Sales[SalesKey]),
CALCULATE(
sum(Sales[SalesAmount])
)
)
RETURN

IF(
ISINSCOPE(Sales[SalesKey]),
IF(
rankSales <= 100,
rankSales,
BLANK()
)
)

And

Top 5 by Profit of the Top 100 by Sales =
var topBySales =
FILTER(
ALLSELECTED(Sales[SalesKey]),
"@rank", RANKX(ALLSELECTED(Sales[SalesKey]), CALCULATE(sum(Sales[SalesAmount])))
),
[@rank] <= 100
)

var rankByProfit =
RANKX(
topBySales,
CALCULATE(SUMX(Sales, Sales[SalesAmount] - Sales[TotalCost]))
)
RETURN
IF(
ISINSCOPE(Sales[SalesKey]),
IF(
rankByProfit <= 5,
rankByProfit,
BLANK()
)
)
The performances of these codes are not the best as can be seen here:

Finally the questions are:

1) Any idea on how to make this code run faster?

2) Any idea on how to achieve step 2 (parametric metrics and parametric top M and N)?

The real issue, also in point 2 are performances.

Thanks in advance for the time and the contributions

Frequent Visitor

P.S.

The dataset has 2.2M rows