Summarizing and Fixing aggregation on a higher level

[motyagi]

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 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 Type	Total cost
A	120
B	30
C	194
D	69

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 ! 

[v-jiascu-msft]

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
    )
)

Best Regards,

Dale

[motyagi]

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]

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