External grouping according to fields

[tommyl]

Hello experts,

I need your help about creating a grouping in the pivot table.

I have a data like:

Table1:

Load

fileName,

Company,

totalActivity,

totalDuration,

totalcharge,

billedActivity,

billedDuration,

billedCharged

from....

I have a graph like:

FileName	Company	TOTAL	BILLED
totalActivity	totalDuration	totalcharge	billedActivity	billedDuration	billedCharged
....	...	....	..	...	..	..	..

I created a pivot table and created a new table like:

Load * inline[

Category, field

'TOTAL', totalActivity

'TOTAL', totalDuration

'TOTAL', totalcharge

'BILLED', billedActivity

'BILLED', billedDuration

'BILLED', billedCharged

]

This turns out to be far from what i need. 

Could you please help me?

Regards,

[Anil_Babu_Samineni]

What is the expected now?

[tommyl]

Hello,

How can i add a field with the values  'Total' and 'Billed' so that when i add this dimension to the pivot table as Column, related group will be done over the measures i sent above. 

[Anil_Babu_Samineni]

Like this?

[tommyl]

Yes, that's the one. 

[Anil_Babu_Samineni]

Check this attachment

If(Match(Category, 'BILLED'), Pick(Match(field, 'billedActivity','billedCharged', 'billedDuration'), Sum(billedActivity), Sum(billedCharged), Sum(billedDuration)),
If(Match(Category, 'TOTAL'), Pick(Match(field, 'totalActivity','totalcharge', 'totalDuration'), Sum(totalActivity), Sum(totalcharge), Sum(totalDuration))))

Or

If(Match(Category, 'BILLED') and Match(field, 'billedActivity'), Sum(billedActivity),
If(Match(Category, 'BILLED') and Match(field, 'billedCharged'), Sum(billedCharged),
If(Match(Category, 'BILLED') and Match(field, 'billedDuration'), Sum(billedDuration),
If(Match(Category, 'TOTAL') and Match(field, 'totalActivity'), Sum(totalActivity),
If(Match(Category, 'TOTAL') and Match(field, 'totalcharge'), Sum(totalcharge),
If(Match(Category, 'TOTAL') and Match(field, 'totalDuration'), Sum(totalDuration)))))))

[tommyl]

Hello Anil, 

I tried your solution but i couldnt manage it. Could you please check below too?:

* I need to keep the table i created in the load editor(with the category and field values), right?

* I need to use either of your expressions as a calculated dimension, right?

* I wrote the dimension names within '', is it correct too?

[Anil_Babu_Samineni]

* I need to keep the table i created in the load editor(with the category and field values), right?
Right

* I need to use either of your expressions as a calculated dimension, right?
Dim 1 - fileName
Dim 2 - Company
Dim 3 - CATEGORY
-- where as Dim 3 created in script with
Load * Inline [
CATEGORY
BILLED
TOTAL
];
Dim 4 - field

Expression -
If(Match(Category, 'BILLED'), Pick(Match(field, 'billedActivity','billedCharged', 'billedDuration'), Sum (billedActivity), Sum(billedCharged), Sum(billedDuration)),
If(Match(Category, 'TOTAL'), Pick(Match(field, 'totalActivity','totalcharge', 'totalDuration'), Sum (totalActivity), Sum(totalcharge), Sum(totalDuration))))

* I wrote the dimension names within '', is it correct too?
I hope, Second point will concur the same