Count of duplicate records for corresponding rows in webi

[former_member334960]

Hi All,

I am working on a webi report where the data is as below:

data.png

and the output should be as below:

output.png

The Scenario here is that a demand can have more than one requirement and can also have more than one resource working on a demand.

If a demand has more than one requirement, the hours are getting duplicated and is showing separately for each requirement. I would like to split the hours based on the number of requirements for any demand and assign them against the resource.

Can someone please help on this?

Thanks.

[former_member334960]

Question has been updated with the correct output image.

[amitrathi239 · Accepted solution]

use this formula.

=[Hours]/Count([Resournce];All) In ([Resournce];[Demand Number])

[former_member334960 · Accepted solution]

Thanks for this, it worked but while testing on different scenarios , noticed that the sum of the split hours is not equal to total hours.

Eg: For one demand, having 5 requirements, has Hours =16, then the split hours=3.2 (16/5) but 3.2+3.2+3.2+3.2+3.2 is not equal to 16hours when we consider 3.2 in time format.

Can you please help in showing the split hours in time format so that the sum of those values will be the total hours.

[amitrathi239 · Accepted solution]

share screenshot.3.2*5 is 16 only.what is the value coming in the report if not 16

[former_member334960 · Accepted solution]

I get 16, but 3.2 is not a time format, if we consider 3.2 as 3hrs and 2min, then when you sum up 3hrs 2min*5 which is not equal to 16.

While dividing itself, can you please help with a formula which will show in time format.

[amitrathi239 · Accepted solution]

can you attach the screenshot?

[former_member334960 · Accepted solution]

In the attached image, the Planned hours=90hours

untitled.png

and after applying the formula, the value for each is 22.5(90/4). But here 22.5 is a number format and is not in time format i.e, if we consider 22.5 as 22 hr and 5 hrs then 22+22+22+22=88hrs and 5 min+5min+5min+5min=20min which will be 88hrs and 10 min though the sum is 90 logically.