Getting Wrong Expression Value

[#1]

Dear Experts ,

i have used add calculated dimensions as my first dimension , based on first dimension , i am getting other dimension and expression value . totally i have 7 expression in report , out of 7 , i am getting correct value on first 4 expression , and last 3 its showing wrong value.

Also i am just doing Sum calculation alone .

Please check my Test data chart , Kindly suggest what i am doing wrong in calculating Billing , GP and GP % . where i am getting correct value for same field in above chart.

=if(([LCL/FCL]='FCL' and (TEU>=$(vAdoch)))or([Transport Mode]='AIR' AND (CHARGEABLE_WEIGHT/1000>=$(vAdoch) )) OR ([Transport Mode]='SEA' AND [LCL/FCL]='LCL' and (VOLUME>=$(vAdoch))), [Origin Country])

[dclark0699 · Accepted solution]

Hi Sriram,

Your Revenue and Volume facts are on different rows in the data model. The rows being returned in your calculated dimension are returned based on the volume. Those rows have no revenue. The easiest way to see this is by adding a filter box for volume. If you filter on any of the volumes, the Billing and GP values go to zero in your top chart.

So you could either adjust your data model so the values are on the row row of data, or you can adjust how you are creating the dimension. I would try to accomplish this via set analysis rather than a calculated dimension.

For instance in the set analysis route, I can take the JobRefs that meet the criteria you have listed, and then return the sum of the fields you are interested in. This will allow us to keep all the rows of data in our SUM. You can see a sample in the new straight table I added in the bottom middle.

<JobRef = P({

                        <[LCL/FCL] = {'FCL'},

                          TEU = {">= $(=vAdoch)"}>

                          +

                         <[Transport Mode] = {'AIR'},

                             CHARGEABLE_WEIGHT = {">= $(=vAdoch)*1000"}

                         >

                         +

                         <[Transport Mode] = {'SEA'},

                             [LCL/FCL] = {'LCL'},

                            VOLUME = {">= $(=vAdoch)"}

                         >

                    } JobRef)>