Calculations and Values Display in webi report?

[Former Member]

Hi,

I have two queries

1. I have created a webi report as below

But in Average and Max columns the values are coming two times.

i have used the calculations for Average and Max. Average = Average(Net Value) in (Org, Dist Channel), Max = Max(Net Value) in (Org, Dist Channel).

I need to show a report like below

How can i achieve this? is this possible to show Average, Max values without duplicates.

2. I need to remove Date and Net Value from the table because customer doesn't want to see these fields in a report. but if i remove this it is coming like below

for getting the correct results do i need to place Net value in Table?

Please help me

[sateesh_kumar1 · Accepted solution]

Hi ,

To get average and max value only once apply break on those columns and merge the values.

[Former Member · Accepted solution]

Hi Sathish,

in crosstab i cannot be able to apply break for Measures and formulas.

[Former Member · Accepted solution]

Hi Kool,

You can try making two custom dimension variables which are equal to your average and max values. Then you will be able to use them to break. For this example this should work because you won't need another aggregation with those measures. If you need you can also use measure versions always.

Also please don't forget to untick"break header" , "break footer" and to tick "merge" option at break options. Finally you can hide date dimension at your block.

Regards,

Onur

[Former Member · Accepted solution]

Hi Onur,

i created two dimension variables for Average and Max.  i tried to apply break on them but it is not working i think this is because of numeric values.

is there any another work around to apply break on measures when we have crosstab?

[sateesh_kumar1 · Accepted solution]

Hi Sudha ,

I didn't noticed it's a cross tab.

In cross tab any way you can't create breaks.

But we can display repeated value once but you will not get formatting as breaks .

If interested , try below

=If (RunningCount([DistC];([DistC];[Month])))=1 Then Max([Netval]) In ([DistC])

[rakeshkumar_bhure]

Hi Sudha,

Can you please try below formula for Avg and max.

I am assuming month as one variable which shows "Jan" in your screenshots.

Average = Average(Net Value in (Org, Dist Channel,)) in (Org, Dist Channel,month),

Max = Max(Net Value in (Org, Dist Channel,)) in (Org, Dist Channel,month).

Just Put Org and Dist Channel in row and month in column. then put above variable into cell.

hope this should work.

Thanks

[Former Member]

Hi Rakesh,

Thank you for the reply.

How can i remove the duplicate values for Average and Max as i mentioned in second image?

[Former Member]

Unfortunately there is no way to merge the cells as long as you have a more granular data element or dimension in the table..

[rakeshkumar_bhure]

If you pull Date and Net value, than average and  max will come as per your first image. you cannot merge it.

[Former Member]

Hi Rakesh/Mishra,

Because i removed net value and Date for Max and Average columns i am getting multivalue and error. how can i rectify this?

[sateesh_kumar1]

Hi Sudha,

Try =average([Net Value] foreach([Date])) in ([Dist Channel];[Month])

     =max([Net Value] foreach([Date])) in ([Dist Channel];[Month])