Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
KrystianW514
New Member

dynamic graph for top n categories and others

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

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

MFelix_0-1676734536616.pngMFelix_1-1676734606505.png

MFelix_2-1676734643458.png

 

See PBIX attach.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
KrystianW514
New Member

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

MFelix
Super User
Super User

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:

MFelix_0-1676734536616.pngMFelix_1-1676734606505.png

MFelix_2-1676734643458.png

 

See PBIX attach.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.