The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello everyone,
I have a base with a lot of products, and i want to show the sales of each product along the time in a line chart. To make the chart more clean, i want to make a dynamic top filter. I used this tutorial : Dynamic TopN made easy
This is the current formula:
TopN_Sales = VAR SelectedTop = SELECTEDVALUE('TopN'[TopN]) // 'TopN'[TopN] is the table with the slicer RETURN IF(RANKX(ALL(base[product]);[Tot_Sales])<=SelectedTop;[Tot_Sales];BLANK()) // Tot_Sales just sum all sales
If the topN are the same across the whole period, the dynamic filter works fine.
I want to make a dynamic topN filter that works in the same way as the TopN filter in visualization. Is there a way to rank by the sum of total sales?
Here is a pbix : Example.pbix
Thank you!
Solved! Go to Solution.
It is unclear on what you mean with "make it work different".
Are you trying to show the 4 items with the highest total sales over a year? or just during the month?
EDIT:
If took your example file and created the following measure:
SubTotal = CALCULATE(SUM(Base[Valor Total]);ALLEXCEPT(Base;Base[marca]))
I then changed your TopN Measure to:
TopN_total_sales = VAR SelectedTop = SELECTEDVALUE('TopN'[TopN]) var RankMarca = RANKX(ALL(Base[marca]);[SubTotal]) RETURN IF(RANKX(ALL(Base[marca]);[SubTotal])<=SelectedTop;[total_sales];BLANK())
I'll get this as a result:
I hope that this solves your problem!
Kind Regards.
PS: If it does solve your problem please mark this as answer.
Hi @rfernandes,
The Visual you've created does exactly what it needs to be done.
It shows the top from that month that's why you'll get blank rows in between because Blue is ranked 4th on may june july and august and orange is ranked 4th on the other months.
Yes.. I know how it is working, but the question is how to make it work different.
It is unclear on what you mean with "make it work different".
Are you trying to show the 4 items with the highest total sales over a year? or just during the month?
EDIT:
If took your example file and created the following measure:
SubTotal = CALCULATE(SUM(Base[Valor Total]);ALLEXCEPT(Base;Base[marca]))
I then changed your TopN Measure to:
TopN_total_sales = VAR SelectedTop = SELECTEDVALUE('TopN'[TopN]) var RankMarca = RANKX(ALL(Base[marca]);[SubTotal]) RETURN IF(RANKX(ALL(Base[marca]);[SubTotal])<=SelectedTop;[total_sales];BLANK())
I'll get this as a result:
I hope that this solves your problem!
Kind Regards.
PS: If it does solve your problem please mark this as answer.
Thank you , it worked perfectly!
In the TopN Measure, the var RankMarca is not used, so i deleted it. And [GrandTotal] should be [SubTotal], right?
Thanks
Hi @rfernandes,
Why is the highlighted value different?
How is the data in source table like? What is the formula of [Tol_Sales]? You said "Tol Sales" sums all sales, but why did you compare it with TopN selection?
Best regards,
Yuliana Gu
Sry, it was a typo. I added the original post some prints and a pbix file.
Have you tried adding the month, the product and measure in a matrix as is and then on the filter pane select the top2.
Measure = tablename[Total Sales]
See image below:
Yes, i want to make it work just like the TopN filter, but i want to make it dynamic.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |