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

 

Resources Table

ID
AAA1
AAA2
AAA3

  

Then, I have the following data table 

 

Allocations Table

IDProjectAllocation (fwd alloc measure)
AAA1sss180%
AAA1sss290%
AAA1sss3160%
AAA1sss4155%
AAA1sss5120%

 

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 

IDAllocation Rollup
AAA1121%

 

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 ?

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

8 Replies

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

    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's avatar
      harrinho
      Icon for Helper III rankHelper III
      8 years ago

      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's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        8 years ago

        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

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    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

      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 ?