Help with Top10 and Others visualization please.

[mjmm]

Dear all, 

I have a summary of what I have so far.

• Prj Nr Name is a column in the '2024 Data' table
• % of Total Planning is a measure that I placed in the '_Measures' table
  
  % of Total Planning = DIVIDE([2024 FY Plan (k€)], CALCULATE([2024 FY Plan (k€)], ALL('2024 Data'[Prj Nr Name])))​
  
• Ranked Projects is a measure that I placed in the '_Measures' table
  
  Ranked Projects =
  RANKX(
  ALL('2024 Data'[Prj Nr Name]),
  [% of Total Planning Charge_EUR 2024 _ PR],
  ,
  DESC,
  DENSE
  ) 
  
  
• Project Label is a measure that I placed in the '_Measures' table
  
  Project Label =
  IF(
  [Ranked Projects] > 10,
  "Others",
  MAX('2024 Data'[Prj Nr Name])
  )

This all results in the following table visualization, that gives me the data I need.

Prj Nr Name	% of total planning	Ranked Projects	Project Label
00490 - Project Q	13,57%	1	00490 - Project Q
00271 - Project W	4,10%	2	00271 - Project W
00171 - Project E	3,82%	3	00171 - Project E
00210 - Project R	3,25%	4	00210 - Project R
00224 - Project T	3,10%	5	00224 - Project T
00845 - Project Y	2,52%	6	00845 - Project Y
00200 - Project U	2,29%	7	00200 - Project U
19984 - Project I	2,19%	8	19984 - Project I
00803 - Project O	2,15%	9	00803 - Project O
00273 - Project P	2,10%	10	00273 - Project P
00770 - Project A	2,05%	11	Others
00130 - Project S	2,02%	12	Others
00279 - Project D	2,01%	13	Others
19982 - Project F	1,80%	14	Others
00290 - Project G	1,74%	15	Others
00291 - Project H	1,61%	16	Others
00172 - Project J	1,60%	17	Others
00260 - Project K	1,56%	18	Others
00281 - Project L	1,54%	19	Others
00230 - Project Z	1,42%	20	Others

What I now need is to create this: 

• A way to keep the top 10 and sum up the rest as 'Others'.
• I want to visualize this, ideally in a pie chart having 11 sections, the top 10 AND all others summed up into 'Others'

Prj Label	% of Total Planning
00490 - Project Q	13,57%
00271 - Project W	4,10%
00171 - Project E	3,82%
00210 - Project R	3,25%
00224 - Project T	3,10%
00845 - Project Y	2,52%
00200 - Project U	2,29%
19984 - Project I	2,19%
00803 - Project O	2,15%
00273 - Project P	2,10%
Others	17,35%

Maybe I have been doing this all wrong since I can't get it to work. I can't put measures in my 'Legend' for the pie chart for example. I am hoping someone can help me here, cause this has been keeping me busy for a while now. 

Any help is much appreciated. 

Thanks a lot!

-Mark

[qqqqqwwwweeerrr]

Hi @mjmm 

This is not the exact solution but with some modification, it might work for you: https://youtu.be/qXLlqikGbRA?si=pLrj8QAEOp-FVDan

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards

[mjmm]

Dear @qqqqqwwwweeerrr, 

I tried using (re-writing) this option and unfortunately I can't get this to work either. All of the sources of help have tested so far keep delivering nothing or just parts that work. I haven't been able to create what is required yet unfortunately.

Thanks for the link to the video though, I saved it anyway for potential future use.

Much appreciated. 

Mark

[mjmm]

Dear @Anonymous, 

Unfortunately I have not been able to solve my issue. 
In the link you will find an example with a randomized dataset. You will find my measures, as well as your proposed solution above already in there. 

https://file.io/F2VKwR2jsBlh 

Hoping someone can help me. 

Thanks again!

-Mark

[Anonymous]

Hi @mjmm ,

It seems that the sample file you share with us has been deleted. Could you share it with again?

And you can show us a screenshot with the result you want. This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mjmm]

Hi @Anonymous, 

The screenshot you posted before in this thread shows the solution I want to achieve. 

Here's another link to my .pbix file
https://we.tl/t-yZfx8Wp2T7 

Thanks again.

[Anonymous · Accepted solution]

Hi @mjmm ,

Here I update the code for calculated column and measure based on your sample.

Project Label_Column = 
VAR _SUMMARIZE =
    SUMMARIZE (
        ALLSELECTED ( 'ExampleDataFile' ),
        'ExampleDataFile'[ProjectNr],
        "% of Total Planning Costs 2024 _ PR",
            DIVIDE (
                CALCULATE (
                    SUM ( 'ExampleDataFile'[Costs] ),
                    'ExampleDataFile'[Status] = "Planning",
                    'ExampleDataFile'[Year] IN { 2024 }
                ),
                CALCULATE (
                    SUM ( 'ExampleDataFile'[Costs] ),
                    FILTER (
                        ALL ( 'ExampleDataFile' ),
                        'ExampleDataFile'[Status] = "Planning"
                            && 'ExampleDataFile'[Year] IN { 2024 }
                    )
                )
            )
    )
VAR _ADDRANK =
    ADDCOLUMNS (
        _SUMMARIZE,
        "Rank", RANKX ( _SUMMARIZE, [% of Total Planning Costs 2024 _ PR],, DESC )
    )
VAR _ADDLabel =
    ADDCOLUMNS (
        _ADDRANK,
        "Project Label", IF ( [Rank] > 10, "Others", FORMAT([ProjectNr],"") )
    )
VAR _CURRENTNAME = 'ExampleDataFile'[ProjectNr]
RETURN
    MAXX(FILTER(_ADDLabel,[ProjectNr] = _CURRENTNAME),[Project Label])

Measure = 
VAR _SUMMARIZE =
    SUMMARIZE (
         'ExampleDataFile' ,
        'ExampleDataFile'[Project Label_Column],
        "% of Total Planning Costs 2024 _ PR",
            SUMX(VALUES(ExampleDataFile[Project Label_Column]),DIVIDE (
                CALCULATE (
                    SUM ( 'ExampleDataFile'[Costs] ),
                    'ExampleDataFile'[Status] = "Planning",
                    'ExampleDataFile'[Year] IN { 2024 }
                ),
                CALCULATE (
                    SUM ( 'ExampleDataFile'[Costs] ),
                    FILTER (
                        ALL ( 'ExampleDataFile' ),
                        'ExampleDataFile'[Status] = "Planning"
                            && 'ExampleDataFile'[Year] IN { 2024 }
                    )
                )
            )
    ))
RETURN
SUMX(_SUMMARIZE,[% of Total Planning Costs 2024 _ PR])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mjmm]

@Anonymous, Wow thanks, this is exactly what I needed. Apologies for the late reply, had some personal things come up unexpectedly. Much appreciated! Thanks!

[Anonymous]

Hi @mjmm ,

According to your statement, I think you want to create a visual based on [Project Label](Legend) and Percentage.

As far as I know, current, Power BI doesn't support us to use measures as a legend, I suggest you to create a calcualted column.

Project Label_Column = 
VAR _SUMMARIZE =
    SUMMARIZE (
        ALLSELECTED('2024 Data'),
        '2024 Data'[Prj Nr Name],
        "% of total planning", [% of total planning]
    )
VAR _ADDRANK =
    ADDCOLUMNS (
        _SUMMARIZE,
        "Rank", RANKX ( _SUMMARIZE, [% of total planning],, DESC )
    )
VAR _ADDLabel =
    ADDCOLUMNS (
        _ADDRANK,
        "Project Label", IF ( [Rank] > 10, "Others", [Prj Nr Name] )
    )
VAR _CURRENTNAME = '2024 Data'[Prj Nr Name]
RETURN
    MAXX(FILTER(_ADDLabel,[Prj Nr Name] = _CURRENTNAME),[Project Label])

Then update your percentage measure as below.

Measure = SUMX(VALUES('2024 Data'[Project Label_Column]),[% of total planning])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mjmm]

Dear @Anonymous, 
First of all thanks for your reply. I implemented your solution and unfortunately I get an error message. 

A circular dependency was detected: 2024 Data[Project Label_Column].

I am not sure how to solve this to be honest.

[Anonymous]

Hi @mjmm ,

Due to I don't know your data model, I could only create a easy sample and have a test.

You can download my attatchment and compare my sample with yours. I hope it could help you solve your issue.

If it still couldn't help you solve your issue, please share a sample file with us.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.