Top N products for top n country and GM%

[Vipin_n]

Hello,

Following measures are available with me on power BI

1. Net Sales $== SUM(fact_actuals_forecast[net_sales_amount])

2. Gross Mmargin $= [NS $] - [Total operational cost]

3. Gross Margin %= DIVIDE([GM $],[NS $],0)

I have fowwing visual reports on dashboard

1. table report= this is showing top 5 countries based on Gross Margin % measure

I want to show top 5 produsts based on top 5 countries in a way
if no country is selected in table report, then it will show top 5 products based on Gross Margin % measure and first country in above table report.
if any country is selected then it will show the top 5 product based on Gross Margin % measure and the selected country.

Thanks,

Vipin

[v-xuxinyi-msft]

Hi @Vipin_n 

@ryan_mayu , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:

My sample:

@Vipin_n , The following formula is just to restore your scenario.

divide = 
VAR _sumOfProfit = SUM('Table'[Profit])
VAR _sumOfSales = SUM('Table'[Sales])
RETURN
DIVIDE(_sumOfProfit, _sumOfSales)

1. Create a calculated table as slicer

slicer = VALUES('Table'[Country])

2. Create a measure as follows

Rank = 
VAR _rankAll = RANKX(ALL('Table'), [divide], , DESC, Dense)
VAR _rank = RANKX(FILTER(ALL('Table'), [Country] IN VALUES(slicer[Country])), [divide], , DESC, Dense)
RETURN
IF(NOT(ISFILTERED(slicer[Country])), _rankAll, IF(ISFILTERED(slicer[Country]) && MAX([Country]) IN VALUES(slicer[Country]), _rank, BLANK()))

3. Put the Rank measure into the visual-level filters, set up as follow

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vipin_n]

Hi,

I guess I'm not able to describe the problem satament correctly, let me include some more details.

Model View :

Measures:

1. NS $== SUM(fact_actuals_forecast[net_sales_amount])

2. GM $= [NS $] - [Total operational cost]

3. Gross Margin %= DIVIDE([GM $],[NS $],0)

Visuals on dashboard:

When user select any one country in visual-1 , then visual -2 shows the top 5 products by selected country and GM%. this is working properly.

Issue: When no country is selected in visual-1,In I want to show the top 5 products by GM% for Country Canada in visual-2 which is the first country in the visual-1.

Thanks,

Vipin

[ryan_mayu]

could you pls provide some sample data and expected output?