Forum Discussion
Helper ITop N with static values
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.
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.
3 Replies
- amitchandak
Super User
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)) 
iabarragancHi. Sorry, it does not work. the same data is still displayed, without filtering only by the required products.
- v-robertq-msft
Community Support
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.
