Display percentage of Itemcount in Bargraph's value axis and Cross Table's Cell Values.

[Sony Hejmady]

My Dataset is :

Business ItemCount

ELECTRONICS 15

ELECTRONICS 15

ELECTRONICS 15

MEDICAL 42

EQIPMENTS 10

EQIPMENTS 11

AGRICULTURE 3

 

Now I need to display Percntage of total Itemcount Over Business for Barchart and Cross Table's cell axis.

1.In Bargraph, I updated the condition :Sum([itemCount])/ Sum([itemCount) OVER (All([Axis.X])) as [Total Item Count]

where the data in Bargraph for each Business isdisplaying correct value.But when I filter for each (Business) from filter panel then it shows 100% for each Business.Please find the attachemnests.

2.Also how to display the Percentage of Itemcount/Total(ItemCount) in Cross Table 's cell values.

[Shashank Gurnalkar]

When you useSum([itemCount) OVER (All([Axis.X]))and filter the data to a single 'Agriculture' value, it is evaluated toSum([itemCount]) itselfwhich makes the result as 100%. If you do not want filters to respond to the denominator, you can use create a calculated column with the expressionSum([itemCount)and use that column in the denominator. That way, the denominator value will always remain static.

To display the values in percentage, you can go to Properties >> Formatting >> Select the Cell value column from the options >> Select Percentage at the bottom.

[Sony Hejmady]

Hi Shashank,

 

In Barchart it is fine.But in Cross table,

 

1)I have a requirement as shown in the attached sheet where I need to calculate Percentage of [Total Item Nr] per Period and Business:

 

Total Item % = [Total Item Nr]/[Gand Total ]  for that particulart Period and Business.

 

Ex: As seen in attached screenshot, for Period=2005,Business=Aviation, Total Item %=747/3328=20%.

 

But I am getting 0% for Aviation with incorrect calculation.

 

My challenge is I cant add over function in cusom expression.

 

Calculated column  T=Sum([Total Item Nr]) OVER ([Period],[bUSINESS]).

 

When I add the column T in the custom axes of cross table ,I need  to add add again Sum function which is:

 

Sum(Total Item Nr/T) which is giving me incorrect % .Please help.

[Shashank Gurnalkar]

If you can share your DXP file with embedded data, I can take a look at it.