Forum Discussion
AdministratorTop N with static values
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.
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
2 Replies
- amitchandak
Super User
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)) 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
