Extract exactly value from row and multiply it with filtered values

[krishna0]

Hello, folks.

I have an issue which bothers me and I know I am somewhere between nothing and the solution but probably lacking the experience. I have a table with allocation and availability (which is just availability from another table where it appears once per Resource - in this table it is multiplied by count of months).

I need to get a number which is a multiplied value of value stored in Availability, but also reacts to count of months (it's per month) and more important - calculates people that are picked up in filter. Each person may have different Availability, but it's the same among months.

What I need is a counter which calculates sum of availability per person per month assuming only one value per person (which is accessible from another table. I even came up with something that works for singe person pick:

SumaProjektowPerMonth = divide(CALCULATE(sum(Allokacje[Availability])), [CurrProjCount],0)*[MonthCount]

but it fails for 2 or more as per below screen. I know that the sum for below two people per one month should be 192 (both have 96). For two months two people should have 384, disregarding actually picked projects (that's why I divided by projects - it multiplied by them).

Thanks in advance for your advise!

Dominik

[Anonymous]

Try to use filtered table via variables. That is,

VAR FilteredPersonTable=ALLSELECTED('personColumn')

VAR ResultTable=ADDCOLUMNS(FilteredPersonTable,"SumOfAvailabilaty",DIVIDE(SUM(Allokacje[Availability]),[CurrProjcount]))

RETURN SUMX(ResultTable,[SumOfAvailability])*[MonthCount]

[krishna0]

Thank you for your reply.

Unfortunately for one person for one year summary it gave me result of 1.95m, where it should state 96 * 12.

Sum of availability is multiplied by count of projects.

#edit:

I managed to retrieve table with single values per month per resource, but I am still stuck there.

[krishna0]

I have evolved a little and came to surprising conclusion that my measure is not working as intended.

Below you see the code - calc count of Project is CurrProjCount - which is 4 but it does not work at all in this calculation, where it is the denominator! So in the very same sheet the very same calculation works and does not work. I have read that it may be relation problem, but this is one table (Maximum value is the one that should be correct).

I even tried to put this as variable but now it shows just 1 instead of 96. The value is still 4.

#edit:

This is ridiculous. 

I use variable - it is 3:

I use only availability which is multiuplicated by number of projects this person is involved druing this month (and it's correct value):

Now I combine both of these and suddenly pCnt does not work...

[krishna0]

Anyone? It seems to be really easy issue but I am too ignorant to notice it.