Forum Discussion

harrinho's avatar
harrinho
Icon for Helper III rankHelper III
8 years ago
Solved

Rollup % to unique ID level

Hi All,    I have a table which holds unique employee IDs and several other columns attached to them and I use this table to get the ID for every employee and drive the visualization (table) I want...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    8 years ago

    Hi harrinho

     

    You may try below measure:

    Rollup % =
AVERAGEX ( SUMMARIZE ( Table2, Table2[ID], Table2[Project] ), [Allocation] )

     

    Regards,

    Cherie

harrinho's avatar
harrinho
Icon for Helper III rankHelper III
8 years ago

It does the aggregation (confirmed it is not the average, it's a sum of the %) itselft that's right, but what I want is to get the count of the IDs that their fwd alloc % is higher thant 100%.

 

The fwd alloc calculation which is what I want to get the ID count from is the following

Fwd Alloc = CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]),'TIME CATEGORY'[Time Category All]="Billable",'WEEK PERIOD'[Time View]<>"QTD")/CALCULATE([FutureAvailable])

 

 

 

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
8 years ago

I would try something like this then

Measure = 
VAR IDs_with_allocations = ADDCOLUMNS(SUMMARIZE(Resources,Resources[ID]),"FwdAlloc",[Fwd Alloc] )
VAR IDs_more_than_100 = FILTER(IDs_with_allocations,[FwdAlloc]>1)
RETURN
COUNTROWS(IDs_more_than_100)

1) it summarizes the IDs with allocation
2) filters the ones that are above 100%
3) counts rows in the filtered table
so, looking at your original example - for AAA1 it will return 1 as 121% is higher than 100%
is this what you were trying to achieve?

  • harrinho's avatar
    harrinho
    Icon for Helper III rankHelper III
    8 years ago

    We are close...It's exactly what I want to achieve but it doesn't return accurate info though. In fact, there are only two IDs with Fwd Allocation %> 100%. Your calc (I created a column instead of measure because I coulnd't physically see the added column) returns two, which is great, but one of the two doens't have >100% Fwd Alloc. He in fact has a low one, 10%.  

     

    What I want to actually do is to put that IDs count (with fwd alloc >100%) in a treemap, display only the value and make it act as a button. So, when you click on that tile (one tile in the tree map, since it is only one measure) to filter the resources table with the list of ID accordingly.