Forum Discussion
Helper IIIRollup % to unique ID level
Hi harrinho
You may try below measure:
Rollup % = AVERAGEX ( SUMMARIZE ( Table2, Table2[ID], Table2[Project] ), [Allocation] )
Regards,
Cherie
Helper IIIThanks 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 :(
Community Championif 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.