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 ,

 

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

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's avatar
    Anonymous
    Not applicable
    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.

     

    • mjmm's avatar
      mjmm
      Frequent Visitor
      1 year ago

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