Chart measure values label

[Broly]

Hi guys,

I am trying to create a horizontal table like this:

How can this be achieved?

Am I missing something?

[rbartley]

You'll need a dimension.  Why don't you load an inline table with column 'row name' and perhaps a sort order column (if you need more than one row as it appears in your Excel example)?  You can then use 'row name' as the dimension.

[Broly]

This works, but how do I pass the value of the row to be interpreted as an expression?

[rbartley · Accepted solution]

So, in the load script you have something like:

[RowLabels]:

Load * InLine [
Sort,RowName
1,US_STD_CU_BILLED
2,FREIGHT_BILLED
3,US_STD_CU_BILLED+FREIGHT_BILLED
4,ACT_BILLED
];

[Data]:
Load * InLine [
Product,US_STD_CU_BILLED,FREIGHT_BILLED,ACT_BILLED
Copper,1000,2000,5000
Fiber,1,2,5
];

Build your pivot table with RowName as the dimension, Product as the column and the measure:

Sum(if(RowName='US_STD_CU_BILLED',US_STD_CU_BILLED,
if(RowName='FREIGHT_BILLED',FREIGHT_BILLED,
if(RowName='US_STD_CU_BILLED+FREIGHT_BILLED',US_STD_CU_BILLED+FREIGHT_BILLED,
if(RowName='ACT_BILLED',ACT_BILLED)
)
)
))

Then sort by Expression:

Sort

See the attached example.

[jbhappysocks · Accepted solution]

An even easier way to do the calculation is to take the Sort field and use it with Pick()

Borrowing the inline loads from Rbartleys post you can get the same result by using.

=sum(
pick(Sort,
US_STD_CU_BILLED,
FREIGHT_BILLED,
US_STD_CU_BILLED+FREIGHT_BILLED,
ACT_BILLED)
)