Rollup % to unique ID level

[harrinho]

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 to create.

Resources Table

ID

AAA1

AAA2

AAA3

Then, I have the following data table 

Allocations Table

ID	Project	Allocation (fwd alloc measure)
AAA1	sss1	80%
AAA1	sss2	90%
AAA1	sss3	160%
AAA1	sss4	155%
AAA1	sss5	120%

which includes multiple rows for each ID, as one ID can work on multiple projects and there is also a calculation which shows the allocation % for each ID per project. 

Then I create a table in power BI which brings in the ID from the Resources Table and the allocation % from the Allocations Tablerolled up in one line

PowerBI viz table 

ID	Allocation Rollup
AAA1	121%

I tried then to create a column in Resources Table so as to bring in this measure but it didn't even give me the choice to bring in a measure. Thus, I created a column in to the Allocations table that would flag with 1/0 for over/underallocatin accordingly. 

OverAlloc = IF([Fwd Alloc]>1,1,0)

This works, but since we have multiple rows for every ID,  this count doesn't really work for me because it is per project and not per ID. Hence, in order to bring in this flag in to the initial Resources Table and be able to get the distinct count for overallocated resources I tried the following  

FWDALLOC Input = LOOKUPVALUE('CLARITY ALLOCATIONS'[OverAlloc],'CLARITY ALLOCATIONS'[ID],'cdl resources monthly'[ID]) 

but apparently I get the error "A table of multiple values was supplied where a single value was expected"

So, the question is, how can I get the aggregaterd Allocation % rolled up per ID in a new column in Resources Table next to the ID ?

[v-cherch-msft · Accepted solution]

Hi @harrinho

You may try below measure:

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

Regards,

Cherie

[harrinho]

Therewas an issue with my relationships which is now fixed but unfortunately we are not there yet.

So, I managerd to bring in the fwd alloc % on to my Resources table which holds unique ID in a single row. In order to get the count of the IDs which are > 100% in Fwd Alloc, I do the following: 

The Over Resources is a Column: 

Over Resoures = IF([Overallocated resources]=1,1,0)

The "Overallocated resources" is a measure:

Overallocated resources = IF([Fwd Alloc]>1,1,0)

Apparently the Overallocated resources is the correct one and this is the column count  I want to get, so as to put in in a tree and make it clickable and when you click the tile, the table will only filter thoses with "1". 

I don't know why I get 0 in the Over Resources column whwen the Fwd Alloc is clearly > 100%. Maybe it's because I changed the Home Table for Fwd Alloc to the Resources Table  which is where i wanted to live in. I did this manually from Modelling-Home Table. 

Isn't there a simple way, even by mocking it up, to get the count of the correct measure ?

[Stachu]

from what I see the roll up that you mention is just an average, is that correct?
if so, and assuming there is join on ID between the tables this will work as measure:

Rollup % = AVERAGE('Allocations Table'[Allocation (fwd alloc measure)])

that's based on assumption that 'Allocation (fwd alloc measure)' is a column in 'Allocations Table'

[harrinho]

Thanks for following up @Stachu. You got the point, there is a join, but the fwd alloc measure is a measure, not a column and is not available as an option in the calculation you suggested. 

The 120% I showed is an average indeed but just for the needs of understanding the problem. In reality is the aggregated Allocation % for the multiple projects to ID level. Not sure if that's the same as an average to be honest 😞 

[Stachu]

if that's the case then it should be sufficent to just drag Resources from 'Resources Table' into visual and then add your measure there, if the join is there it should calculate the aggregation on it's own

alternatively you can add calculated column in 'Resources Table' using the measure reference, but if the above solution doesn't work it's not gonna change much
without the syntax of the % allocation measure I cannot do more

[harrinho]

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]

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]

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.