how to add extra line to pivot table and include it in total calculations?

[PolBe]

Hello,

I have the following challenge: 

I need to have a pivot table where I calculate the amount of $ per category per phone call + fixed charge

LOAD * INLINE [
Category,Call ID, YYYY\MM, 
New customer, 1, 2019\01
New customer, 2, 2019\02
New customer, 3, 2019\03
Change password, 4, 2019\03
Change password, 5, 2019\04
Invoice issues, 6, 2019\05
Invoice issues, 7, 2019\06
Invoice issues, 8, 2019\07
];

LOAD * INLINE [
Category, Price
New customer, 10
Change password,15
Invoice issues,20
];

then I calculate how much each call cost depending on a category:

Count(distinct [Call ID])*Price

how can I add extra line 'Fixed charge' under each month and calculate it in the totals like below?

120 $ per year --> 10 $ per month:

Greetings,

Polina

[lockematthewp]

You can just manually add those lines in the Inline Tables. Like below:

LOAD * INLINE [
Category,Call ID, YYYY\MM,

........
Fixed Charge, 1, 2019\01
Fixed Charge, 2, 2019\02
Fixed Charge, 4, 2019\03
Fixed Charge, 5, 2019\04
Fixed Charge, 6, 2019\05
Fixed Charge, 7, 2019\06
Fixed Charge, 8, 2019\07

.........
];

LOAD * INLINE [
Category, Price
New customer, 10
Change password,15
Invoice issues,20

Fixed Charge, 10
];

[Arthur_Fong · Accepted solution]

I did some modifications in the script to get this result:

Fixed Charge are added accordingly as your data expand to a new month. 

Since there is no data for Aug, Sep, Oct etc, Fixed Charge for these months are not added into the table. 

Refer qvw below for reference.

Thanks and regards,

Arthur Fong

[PolBe]

hi, 

thanks for your suggestion. I also had this in mind but then I would need to do manual work every month. the suggestion from Arthur Fong excludes any manual work .

thank you anyways 🙂

greetings,

Polina

[PolBe]

Hi Arthur ,

brilliant solution!

it works. thanks a lot!!!

Greetings,

Polina