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.

     

mjmm's avatar
mjmm
Frequent Visitor
1 year ago

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'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.