Top N with static values

[iabarraganc]

Hi.

I have a dimension table with the attributes of a product, and another table (also with dimensions) with the warehouses where it is sold.

I need to generate initially a top of which are the products that sell the most at a general level. For the example I have a top of the 3 best selling products.

Product	Value
Product A	$ 16,500.00
Product B	$ 3,200.00
Product C	$ 1,800.00
Total	$ 21,500.00

Then I need to generate a matrix where the distribution by warehouse of those selected products is indicated. The following result should be obtained.

Warehouse	Product	Value
Warehouse 2	Product A	$ 10,000.00
Warehouse 3	Product B	$ 500.00
Warehouse 4	Product C	$ 100.00
Warehouse 3	Product A	$ 5,000.00
Warehouse 4	Product B	$ 1,700.00
Warehouse 5	Product C	$ 600.00
Warehouse 4	Product A	$ 1,500.00
Warehouse 5	Product B	$ 1,000.00
Warehouse 6	Product C	$ 1,100.00
Total		$ 21,500.00

But at the moment of calculating the matrix, the top of the products for each warehouse is appearing, and the products that are NOT in the initial filter (A, B, C) are shown. The total value IS OK, but the detail is not (the products X, Y, Z should not even appear).

Warehouse	Product	Value
Warehouse 2	Product A	$ 3,800.00
Warehouse 3	Product B	$ 2,500.00
Warehouse 4	Prodcut Z	$ 589.00
Warehouse 3	Product X	$ 8,792.00
Warehouse 4	Product Y	$ 4,256.00
Warehouse 5	Product A	$ 2,587.00
Warehouse 4	Product C	$ 950.00
Warehouse 5	Product B	$ 671.00
Warehouse 6	Prodcut Z	$ 645.00
Total	**Real value 24,790**	$ 21,500.00

I have used the RANK(), TOPN functions to calculate the values. And when I think the calculation is going to come out it breaks. This is the formula I used

VALOR_INICIAL_TOP_N_ORIG = 
VAR TOP_TABLA = TOPN(3, ALLSELECTED(AUX_MARCA), [#VALOR_HECHOS_INICIAL])
VAR VALOR_TABLA = CALCULATE([#VALOR_HECHOS_INICIAL], KEEPFILTERS(TOP_TABLA))
RETURN
VALOR_TABLA

I appreciate your collaboration to solve this, since I can't find how to do it.

[v-robertq-msft · Accepted solution]

Hi, 

According to your description, I can roughly understand your requirement, you want to generate initially a top of which are the products that sell the most at a general level like top 3, right? I think you can try this method to achieve your requirements:

This is the test data I created based on your description:

You can create a measure [Flag] like this:

Flag =

var _summarize=SUMMARIZE(ALL('Table'),'Table'[Product],"Value",SUM('Table'[Value]))

var _top3=TOPN(3,_summarize,[Value],DESC)

var _product=SELECTCOLUMNS(_top3,"1",[Product])

return

IF(MAX('Table'[Product]) in _product,1,0)

And you can go to create a table chart and apply the visual filer to get what you want, like this:

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[iabarraganc]

Hi. Sorry, it does not work. the same data is still displayed, without filtering only by the required products.

[amitchandak]

@iabarraganc , Assume value in measure , Try like

Measure =
var _tab = TOPN(3,allselected(table[Product]),[Value],Desc)
return
calculate([Value], filter(table, table[Product] in _tab))