DAX question - Filter along with Top N

[vmakhija]

Hello

I have a unique requirement where user will have 2 slicers -

1. To select top n (options can be top 3, top 5, top 7, etc)

2. Debt or Non-Debt (data regarding this - I will explain below) - this will be single select

Consider the snapshot of the sample data as below -

Company	Market Cap	Debt/No Debt	Date
Orange	4.9	No Debt	8/31/2020
Orange	2.9	No Debt	8/30/2020
Orange	6	No Debt	8/29/2020
Orange	9.2	Debt	8/28/2020
Orange	8.4	Debt	8/27/2020
Orange	2.8	Debt	8/26/2020
Orange	2.1	No Debt	8/25/2020
Orange	4.2	Debt	8/24/2020
Orange	8.1	Debt	8/23/2020
Orange	0.9	Debt	8/22/2020
Orange	0.3	No Debt	8/21/2020
Orange	3.3	No Debt	8/20/2020
Orange	7.2	No Debt	8/19/2020
Orange	5	No Debt	8/18/2020
Orange	3.5	Debt	8/17/2020
Orange	8.5	No Debt	8/16/2020
Orange	8	Debt	8/15/2020
Orange	5.2	No Debt	8/14/2020
Orange	7.2	No Debt	8/13/2020
Orange	9.1	Debt	8/12/2020
Orange	6.2	No Debt	8/11/2020
Orange	4.4	Debt	8/10/2020

There can be 2 scenarios -

1. If user selects "Debt" and top 5, the expected output is 

Company	Cumulative MarketCap	Date
Orange	16.2	8/31/2020
Orange	14.6	8/30/2020
Orange	18.9	8/29/2020
Orange	17.9	8/28/2020
Orange	17.9	8/27/2020

In this case, only the top 5 dates where Debt/No Debt is "Debt" will be considered. Thus, it shows cumulative marketcap of top 5 Debt dates corresponding to each date on a rolling basis.

2. If user selects "No Debt" and top 5, the expected output is 

Company	MarketCap	Date
Orange	32.7	8/31/2020
Orange	32.7	8/30/2020
Orange	32.7	8/29/2020
Orange	32.7	8/28/2020
Orange	24.4	8/27/2020

In this case, only the top 5 dates where Debt/No Debt is "No Debt" will be considered. Thus, it shows cumulative marketcap of top 5 "No Debt" dates corresponding to each date on a rolling basis.

I tried to use TOPN in my measure, but didn't get it to work.

Anyone tried this use case before or have any ideas?

Regards

[amitchandak]

@vmakhija , refer if this can help

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[vmakhija]

thanks @amitchandak for providing these useful links.

Actually, in my case, the dates will be not continous, so I am having a problem to use RANKX function.

Regards

[TomMartens]

Hey @vmakhija ,

please explain the expected outcome

• the dates seleceted if the user is selecting top 5 and top 3 no debt and each time the calculation for the cumulative market cap
• the dates seleceted if the user is selecting top 5 and top 3 debt and each time the calculation for the cumulative market cap

make sure that the explanation matches the sample data you provided.

I'm also wondering if there will be more than one company and if the data of a 2nd company will affect the calculation of the cumulative market cap and the selection of dates.

Regards,

Tom

[vmakhija]

@TomMartens 

thanks for your reply

Here is an explanation -

1. the dates seleceted if the user is selecting top 5 and top 3 no debt and each time the calculation for the cumulative market cap

In this case, the dates selected for the calculation has to be -

a. for Top 5 and "No debt", it will be 8/31/2020, 8/30/2020, 8/29/2020, 8/25/2020 and 8/21/2020.

b. for Top 3 and "No debt", it will be 8/31/2020, 8/30/2020 and 8/29/2020.

2. the dates seleceted if the user is selecting top 5 and top 3 debt and each time the calculation for the cumulative market cap

In this case, the dates selected for the calculation has to be -

a. for Top 5 and "debt", it will be 8/28/2020, 8/27/2020, 8/26/2020, 8/24/2020 and 8/23/2020.

b. for Top 3 and "debt", it will be 8/28/2020, 8/27/2020 and 8/26/2020.

Yes, there will be more than one companies.

The data for one company will be mutually exclusive from another company. Each company's data will be looked at as 1 group for the DAX calculation.

Regards

[TomMartens]

Hey @vmakhija ,

please provide the calculation for the cumulative Market Cap for Top 5 No debt and Top 5 Debt as the value for the expected result from your initial post are not totally clear, at least not to me.

Regards,

Tom

[vmakhija]

@TomMartens 

Here is the calculation.

1. For Top 5, No Debt, it will be 4.9 + 2.9 + 6 + 2.1 + 0.3 = 16.2

8/31/2020 -> 4.9

8/30/2020 -> 2.9

8/29/2020 -> 6

8/25/2020 -> 2.1

8/21/2020 -> 0.3

2. For Top 5, Debt, it will be 9.2 + 8.4 + 2.8 + 4.2 + 8.1 = 32.7

8/28/2020 -> 9.2

8/27/2020 -> 8.4

8/26/2020 -> 2.8

8/24/2020 -> 4.2

8/23/2020 -> 8.1

Hope it is clear now.

Basically, I want to rank the non-consecutive dates based on the "Debt/No Debt" filter.

And then use the top 5 or top 3 or top n using this rank. Then calculate the cumulative market cap.

Regards