Pivot Table custom Display of Rows and Columns

[MMK]

I am having data for two Quarters. I want to show the data into a pivot table for two quarters at Column level.

Here from data we are getting two accounts 987024,987011  and its subdivisions (like 987024A, 987024B,987024C). I want to bring another account which is 987025 which is the ration of 987024 and 987011

(987025 = 987024/987011).

and i am having another mapping table, to bring summary name from AN.

Now my final requirement, I want to show a pivot table below.

In Pivot table,

Rows - Summary, AN, State (Row Hierarchy Level1 --> Summary, Level2 --> AN and Level3 --> State)

Column - Period

Posted same type of question Previously, but now different data sets (Data Inputs), but expecting same output as old query.

Attaching excel file with sample data ( 3 sheets - Fact table, Mapping table and Requirement sheets)

[Sayed_Mannan]

Hi MMK, the work was mostly simple, but has one tricky part.

1. I separated AN into base numbers (like 987024) and A/B/C codes (like 987024A).

2. I converted A/B/C codes back to their real BaseAN (remove the last letter).

3. I matched every BaseAN with its Summary group using your mapping table.

4. The only tricky step was using only BaseAN values in totals and completely ignoring A/B/C values — this made the numbers match your Excel output exactly.

5.After that, making the pivot table was easy.

here is the script what I did:

MapSummary:
MAPPING
LOAD
If(IsNum(AN), AN, Left(AN, Len(AN)-1)) as BaseAN,
Summary
FROM [lib://testJass/Sample Data v3.xlsx]
(ooxml, embedded labels, table is [Mapping Table]);

LOAD
Period,
Country,
State,
AN,
Num(Value) AS Value,
If(IsNum(AN), 1, 0) as IsBase,
If(IsNum(AN), AN, Left(AN, Len(AN)-1)) as BaseAN,
ApplyMap('MapSummary', If(IsNum(AN), AN, Left(AN, Len(AN)-1)), 'UNMAPPED') as Summary
FROM [lib://testJass/Sample Data v3.xlsx]
(ooxml, embedded labels, table is [Final Dat Set 3]);

here is the sample result in my test environment:

This is the data I'm using in the pivot table:

If this works for you, please mark this as the solution.

[MMK]

Hi @Sayed_Mannan ,

Thanks for your reply. 

My requirement is different. In addition to existing two accounts i want to show another account 987025. We need to calculate that ratio as 987025 = 987024/987011. I want to show that account in Pivot table.

and Accounts names 987024A..e.t.c different numbers in my case just for easiness i gave those numbers.

[Qrishna]

IF(Only(Summary) = 'ANSTER'
, IF(Dimensionality() = 3 // State rows
, Num(Sum({<Summary={'ANSTC'}>} TOTAL <Period, State> Value)/Sum({<Summary={'ANSIG'}>} TOTAL <Period, State> Value), '#.######')
, Num(Sum({<Summary={'ANSTC'}>} TOTAL <Period> Value)/Sum({<Summary={'ANSIG'}>} TOTAL <Period> Value), '#.##') )// Total row (under ANSTER)
, Sum(Value))