dynamic graph for top n categories and others

[KrystianW514]

Hello everyone!

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?

https://drive.google.com/file/d/1fVFAF02uI0Rtex-OcTxFzcE64enqF4OA/view?usp=sharing

[KrystianW514]

It works great! It will take me a while to fully understand how it works, but thank you so much for your help 🙂

[MFelix · Accepted 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 =
    SELECTCOLUMNS (
        TOPN (
            [Top N Value],
            SUMMARIZE (
                Arkusz1,
                Arkusz1[industrycodename],
                "SalesTotal", SUM ( Arkusz1[Total] )
            ),
            [SalesTotal], DESC
        ),
        "Industry", Arkusz1[industrycodename]
    )
VAR sales =
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Top N  + Others'[industrycodename] ) = "Others",
            SUM ( Arkusz1[Total] )
                - CALCULATE ( SUM ( Arkusz1[Total] ), Arkusz1[industrycodename] IN TOPNVALUES ),
        SELECTEDVALUE ( 'Top N  + Others'[industrycodename] ) IN TOPNVALUES,
            CALCULATE (
                SUM ( Arkusz1[Total] ),
                Arkusz1[industrycodename] IN DISTINCT ( 'Top N  + Others'[industrycodename] )
            )
    ) --, SUM(Arkusz1[Total]))
RETURN
    IF ( ISINSCOPE ( 'Top N  + Others'[industrycodename] ), sales, SUM ( Arkusz1[Total] ) )

Use the column from the Top N + Others and the metric:

See PBIX attach.