Skip to main content
cancel
Showing results for 
Search instead 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

Reply
IMasalhaBI
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

IMasalhaBI_0-1685633998651.png

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(
    ADDCOLUMNS(
        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:
IMasalhaBI_1-1685634757394.png

 

 

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

1 REPLY 1
IMasalhaBI
Frequent Visitor

P.S.

The dataset has 2.2M rows

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.