I am very pleased to join this community.
I have problem with creating dynamic graph for the top n categories and their coresponding sales totals and for 'other' category.
I have spent many hours on that topic, I tried many codes from this community and other websites, but I still facing the problem where my graph doesn't work with year filter from slicer.
In the report (link below - I cannot add any attachment to the message) I created calculated table to show the expected result.
I still have problems with fully understanding measures, so probably this is the cause for the whole situation.
Could someone suggest how to make it so that selecting a year generates a new chart of the top categories?
Go to Solution.
Hi @KrystianW514 ,
For this you need to create a table with the industry code names and a row with others:
Top N + Others = union(DISTINCT(Arkusz1[industrycodename]), Row("industrycodename", "Others"))
I also created a parameter for the selection of the topn to be dynamic.
Now add the folllowing measure to your model:
Total TOPN =
VAR TOPNVALUES =
[Top N Value],
"SalesTotal", SUM ( Arkusz1[Total] )
VAR sales =
SELECTEDVALUE ( 'Top N + Others'[industrycodename] ) = "Others",
SUM ( Arkusz1[Total] )
- CALCULATE ( SUM ( Arkusz1[Total] ), Arkusz1[industrycodename] IN TOPNVALUES ),
SELECTEDVALUE ( 'Top N + Others'[industrycodename] ) IN TOPNVALUES,
SUM ( Arkusz1[Total] ),
Arkusz1[industrycodename] IN DISTINCT ( 'Top N + Others'[industrycodename] )
) --, SUM(Arkusz1[Total]))
IF ( ISINSCOPE ( 'Top N + Others'[industrycodename] ), sales, SUM ( Arkusz1[Total] ) )
Use the column from the Top N + Others and the metric:
See PBIX attach.
Proud to be a Super User!
View solution in original post
It works great! It will take me a while to fully understand how it works, but thank you so much for your help 🙂
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.