% of total row pivot table

[sanket94]

I am trying to make a pivot table using the count of stores. But the % of total row should be by Service. 

I tried using the total function, but still, I am not able to use it.

Trying to achieve something like below:

Legacy Pool	100.00%	0.00%	0.00%	100.00%
Small Parcel Direct	93.83%	3.70%	2.47%	100.00%
Small Parcel Exception	62.50%	25.00%	12.50%	100.00%

But on QLik:

Service	Discrepancy_+/-1?	Match	Overage	Underage
Legacy Pool	5178200%	2468400%	7010800%
Small Parcel Direct	9899500%	4719000%	13403000%
Small Parcel Exception	761500%	363000%	1031000%

Could someone please help with this. Appreciate your help in advance 

[sunny_talwar]

I am not seeing the data on the pivot sheet

Is this based on a certain selection?

[sanket94]

Hi,

I want to keep this table flexible enough to change date and make other selections as required.

I sm trying to get percentages by row, Currently, totals are over 100%. Please check the attached capture from excel that i am trying to replicate.

Appreciate your help 

[sunny_talwar · Accepted solution]

Try this

Count([Store #])/Count(TOTAL <Service> [Store #])

 

[sanket94]

Hi, 

It provided a garbage value it seems all the Percentages show up the same.  I have attached the screenshot below

I appreciate your help and quick responses.

[sunny_talwar]

Have you tried exporting your chart to an Excel and performing the calculation?

Excel gives the same repeating numbers for all rows. Are you familiar with the principal "Garbage in, garbage out"? If I did this incorrectly in Excel, can you show me what the right way to do this calculation in Excel?

[sanket94]

Hi,

Thank you for sending that excel sheet. It seems  I am making some mistake in the pivot table itself. When I tried the same excel sheet shared by you percentages seems to be working. Now as you shared file i could find the cause of the issue it seems like the issue is in the data load editor for column disperancy +- 1 . I was extracting the data from the table below the pivot and used the formula =IF((Estimated-Delivered)>1,'Underage', IF((Delivered-Estimated )>1,'Overage','Match'))

But when I use the same formula in data load editor the answer is changing. 

Attached is the excel sheet from the table below the pivot table

[sunny_talwar]

I am not sure what is wrong, but if I load your Excel using this script

LOAD
    Service,
    Estimated,
    Delivered,
    "Store #",
    If(([Estimated]-[Delivered]) > 1, 'Underage',
         If(([Delivered]-[Estimated]) > 1, 'Overage', 'Match')) as [Discrepancy_+/-1?]
FROM [lib://Lib/pivot1.xlsx]
(ooxml, embedded labels, table is Sheet1);

I get the correct result

[sanket94]

When  I use the same formula in the table directly, It works perfect on the table. But when I put on data load editor it's creating all 3 lines per key. Please review below, Let me know if you have any insights on the same. 

The formulated column 3 is working as expected with the correct answer. I really appreciate you helping me with this 

[sunny_talwar]

Something to do with Many to Many join may be? Can you share [lib://PCS SHARED/FinalMaster.QVD]?