cancel
Showing results for 
Search instead for 
Did you mean: 

How to display Total and Grand Total in Section - Webi 4.0

Former Member
0 Kudos

Hi All,

Need your help with the below requirement in Webi 4.0

We have Gross Sales Value with a hierarchy of District Number and Store NUmber

when we put a section on Store Number to display the Gross Sales value, we need to show Gross Sales Value at the Store and also total at District Level

Store level works without any issue

but how to show district level total in section

Tried with below

=[Gross Sales Value] ForEach([District number])

the above only displays at specific store level

=[Gross Sales Value] ForEach([District number]) In Report

it displays total value in the report but won't split at district level

really appreciate any inputs on this..

it's a bit long description, thanks for your time and help

Accepted Solutions (0)

Answers (1)

Answers (1)

saurabh_raheja
Participant
0 Kudos

Can you try for this and let me know

[Gross Sales Value] ForEach([District number] In Section

Former Member
0 Kudos

Hi Saurabh,

Thanks for your quick reply.

I'm getting the store(section) level total with the context you have shared. Value is same with or without this context in the section.

I need to get the total at District level where as Section is on Store. Please see a bit detailed requirement as below.

If we have values like this

District         Store     Gross Sales Value

----------      ---------     -------------------------

District 1    Store 1       100

District 1    Store 2       200

District 1    Store 3       300

Requirement to show as below.

Store 1 (Section)

Gross Sales Value          Gross Sales Value at District Level

-------------------------          -----------------------------------------------

100                                                            600

Store 2 (Section)

Gross Sales Value          Gross Sales Value at District Level

-------------------------          -----------------------------------------------

200                                                            600

Store 3 (Section)

Gross Sales Value          Gross Sales Value at District Level

-------------------------          -----------------------------------------------

300                                                            600

Please let me know if you need any additional details.

Thanks again.

Amar

former_member199945
Active Contributor
0 Kudos

Hi,

Create new measure for gross value as v1 =sum([Grosssalevalue]) in([District])

Now take this measure in report  and in section formula for v1 apply  as [v1] forall [district]

I checked it worked  see below .

If you don't want to show dist in report then you can use created variable directly and no need to use formula in section.

Thanks.

0 Kudos

Hi Amar,

Why don't you try applying break on the District Level?

So that you will get the consolidated result at the District Level automatically rather than applying any formula.

Hope it helps

Regards,

Varsha

Former Member
0 Kudos

Hi Seshu,

Thanks for your detailed reply. I have tried creating a variable with Gross Sales Value at district level same as you have done. But it didn't help.

Please see the below. This is from Deski, which I could get the values in required format.

8001, 8308, 8370, 8400, 8443 are different Store Numbers on which section is there and all of the stores belong to same District Number 89.

Second  column on Gross Sales Value is at Store Level and third column is at district level.

We need to get the same in Webi. Could you please check this and let me know.

Thanks a lot for your help.

Amar

Former Member
0 Kudos

HI Varsha,

Thanks for your reply.

Requirement is to display many other details at store level and we need to create a section on Store. And Gross Sales Value is one value among them.

Creating the Break on District Number will not help us in this requirement.

Thanks

Amar

saurabh_raheja
Participant
0 Kudos

Please try [Gross Sales Value] IN ([District number] In Report and let me know if it works

former_member199945
Active Contributor
0 Kudos

Hi,

Can you share the formula that you have  used in webi report.

Becoz may be you have  wrongly defined  with the sample data what you have provide base don that I created if it works for me it will definitely works for you .

One more thing after created variable used in report did you applied =[v1] forall (district )?  as above screen shot. If you have not done do it.

Thanks

Former Member
0 Kudos

Hi Saurabh,

It still shows the total value at report level.

Tried adding that parenthesis as well, but result is same.

=([Gross Sales Value] In ([* District number])) In Report

Thanks

Amar

Former Member
0 Kudos

Hi Seshu,

Variable is created as below.

=[Gross Sales Value] In ([District number])

But i can't use ForAll as it's a single district number, but multiple stores. Please see the sample dataset i have shared from Deski.

Thanks

Amar

former_member199945
Active Contributor
0 Kudos

Hi Amarnadh ,

create sum of gross value as v1 =sum([grossvalue] ) in( [district no])

use this variable in report and now apply forall(dist no) as above screen shot  like [v1] forall distno

if it not works then try forall with store no.

Thanks

Former Member
0 Kudos

Hi Seshu,

sum([grossvalue] ) in( [district no]) giving me total value in Report. So I have removed Sum function for the variable definition.


I can't use ForAll for District Number as I need to get the sum at each district. Tried ForAll with Store Number. This gave total value in report again. Tried the combination of ForAll Stores with In/ForEach District. It didn't help.


Thanks

Amar


former_member199945
Active Contributor
0 Kudos

What total it is showing is it  Gross sale value at district level or different value ?

Thanks

Former Member
0 Kudos

Hi Seshu,

It's giving the total Gross Sales Value at report level when we use ForAll with Store Number. As informed, I have tried this with a combination of ForEach District and In District.

But it didn't help.

Thanks

Amar

Former Member
0 Kudos

Hi,

If my understanding is correct, you have 2 levels of data and you have created a section at the second level and showing the aggregation at the 2nd level and also you want to show the aggregation of 1st level also at the second level.

Create a section 1st dimension, 2nd dimension

If you keep Sum([Measure]) at the second level then it will automatically gives the required aggregation,

Then create one more formula to get the 1st level aggregation as Sum([Sales revenue] )ForAll([Level1 Dimension])

In my example, i have create Section on State and Year.

Just sum[Measure] shows aggregation at year level , sum[measure] for all (Year) gives the state level aggregation. Which is same for each year in a particular State.