Hi Experts,
I have a requirement in Pivot table calculated dimension only like below
Calculated dimension -> Rate
=If( %key_ID= '$(vDefaultkeyID)' AND [sheettype] = 1,Text(num(0)), Text(([Rate])))
Calculated dimension -> Time
=num(aggr(sum(Overtime),Region,Team,EmpType,PName,TName),'#,##0.00')
Here my requirement is to create another calculated dimension by multiply the Rate and Time calculated dimensions
I tried like below types but getting 0 value or N/A
=num(aggr(sum(Overtime)*(If( %key_ID= '$(vDefaultkeyID)' AND [sheettype] = 1,Text(num(0)), Text(([Rate])))),Region,Team,EmpType,PName,TName),'#,##0.00')
=If( %key_ID= '$(vDefaultkeyID)' AND [sheettype] = 1,Text(num(0)), (Text(([Rate]))*(num(aggr(sum(Overtime),Region,Team,EmpType,PName,TName),'#,##0.00')))
Below the sample output getting but if anything *0 should come 0
| Rate | Time | Cost |
| 92.400000 | 36.000 | 3,326.400 |
| 92.400000 | 5.000 | 462.000 |
| 0 | 9.000 | 782.910 |
| 0 | 9.000 | 782.910 |
| 0 | 9.000 | 120.420 |
Need to achieve like below table
| Rate | Time | Cost |
| 92.400000 | 36.000 | 3,326.400 |
| 0 | 9.000 | 0 |
| 0 | 9.000 | 0 |
| 0 | 9.000 | 0 |
| 92.400000 | 5.000 | 462.000 |
| 92.400000 | 0 | 0 |
Kindly help me to achieve
