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 No Project Type Total cost  Invoiceable  12 A 100 Y 1 A 20 Y 2 B 30 N 45 C 44 N 78 D 69 N 12 C 100 N 4 C 20 N 5 C 30 Y   I wa...
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