Forum Discussion

motyagi's avatar
motyagi
Icon for Helper I rankHelper I
8 years ago

Summarizing and Fixing aggregation on a higher level

DATA 

Project NoProject TypeTotal cost Invoiceable 
12A100Y
1A20Y
2B30N
45C44N
78D69N
12C100N
4C20N
5C30Y

 

I want to be able to pick the top N (dynamic ) project Types based on Total Cost 

So for example i want it to summarize on Project Type first 

 

Project TypeTotal cost 
A120
B30
C194
D69

 

then the top 1 would be C - 194

top 2 would be C and A with 194 and 120 resp. 

And i want this top N to not be a measure as I need to put this on a donut chart or visual and measure can't be used as legends on visuals .

 

 

PLEASE HELP ! 

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi motyagi,

     

    You can add a calculated table like this.

    SummarizedTable =
SUMMARIZE (
    'Table1',
    Table1[Project Type],
    "Total", SUM ( Table1[Total cost] ),
    "top n", RANKX (
        ALL ( Table1 ),
        CALCULATE (
            SUM ( Table1[Total cost] ),
            ALLEXCEPT ( Table1, Table1[Project Type] )
        ),
        ,
        ,
        DENSE
    )
)

    Summarizing_and_Fixing_aggregation_on_a_higher_level

     

    Best Regards,

    Dale

    • motyagi's avatar
      motyagi
      Icon for Helper I rankHelper I
      8 years ago

      Thank you ! I got to this point but I'm stuck with the labels after the rank function . 

       

      I want to lable the top 2 projects as  - In assessment when 2 is selected. 

      This 2 is coming from another excel file which is an input parameter file 

       

      Label = IF([Rank] > SELECTEDVALUE(Input_Para[Projects_Included_In_View]),"Excluded ","Included ")

       

      this doesn't work !

      Is it cause the input_para table is not linked to my table 

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi motyagi,

         

        What's the type of "Label"? A measure or a calculated column?

        Did you select any items in the slicer? Can you share some snapshots or the pbix file?

         

        Best Regards,

        Dale