Forum Discussion

mjmm's avatar
mjmm
Frequent Visitor
1 year ago
Solved

Help with Top10 and Others visualization please.

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 Plan...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
mjmm
Frequent Visitor
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    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.