Top N with static values

[Syndicate_Admin]

Hello

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

It is required to initially generate 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 products that sell the most.

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

Then it is necessary to generate a matrix where it is indicated of those selected products, the distribution by warehouse of the same. 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 time 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 IF OK, but the detail is not (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 used the functions of RANK(), TOPN 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.

[Anonymous · Accepted solution]

Hi  @Syndicate_Admin ,

I created some data:

You can use tabs to display:

Here are the steps you can follow：

1. Create measure.

VALOR_INICIAL_TOP_N_ORIG =
var _Table=SUMMARIZE(ALL('Table'),'Table'[Product],"Total",SUM('Table'[Value]))
var _TOP_TABLA=TOPN(3,_Table,[Total],DESC)
return
IF(
    MAX('Table'[Product]) in SELECTCOLUMNS(_TOP_TABLA,"1",[Product]),
    1,0)

2. Place [VALOR_INICIAL_TOP_N_ORIG]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@Syndicate_Admin , Try like

Assume value in measure , Try like

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