Wrong Calculation in Crosstab

[Former Member]

Just go through the scenario

u2022 If we have 3 dimensions (Year,State and City) and 1 Measure (Sales revenue) in query

u2022 In croostab we are displaying measure for state and Year only (State in columns and Year in Top Row)

Year

State Measure

u2022 If I want to add 100 with existing measure and display in next column like

Year

State Measure Measure+100

u2022 The problem is, it is not adding 100 to stateu2019s aggregated measure, If there are 5 cities under a state then 500 is added (in each cityu2019s revenue 100 is added then aggregating stateu2019s revenue even if we are not including the city in report) and displaying wrong result

Any Idea how to resolve this issue?

Thanks in Anticipation

[Former Member]

Hi Sadick,

Try below Steps:

1. Create a Formula u2018Measure+100u2019 which will add 100 to the measure

For instance:

{query1.Sales revenue} +100

2. Go to cross-Tab Expertu2026

3. Add this formula u2018Measure+100u2019 to the summarized Fields:

It will be shown as Sum of @Measure+100

If you add city to the columns, you will see that in each city revenue 100 is added.

Regards,

Alpana

[Former Member]

Hi Sadick,

You can achieve the same effect by creating custom cross-tab. A custom cross-tab is a regular report that is built and formatted to look like a cross-tab. Custom cross-tabs are handy because they allow more report design flexibility than cross-tab reports do. However, its drawback is that it doesn't generate columns dynamically. The columns will be static and each column on the report requires a separate formula to display its corresponding row data.

For more information regarding Custom Cross-tab, search for the article u201CHow to create a custom cross-tab with percentage valuesu201D.

Regards,

Alpana

[Former Member]

Hi Sadick

Try creating a formula field and apply the required calculation in it.

Add this formula field as a column in your cross-tab.

Hope this helps!!!

Regards

Sourashree

[Former Member]

Hi

Thanks for your input,

I have used formula field only, still the issue happening