Pivot is not calculating correct month wise count

[shaikhrulez]

Hello everyone,

Please find attached a simple table with following columns.

• Region
• Branch Category
• BOM Status as of 30 June, 2021
• BOM Status as of 31 July, 2021
• BOM Status as of 31 Aug, 2021

I am trying to make a pivot table with Region wise summary of Category II and Above Branches which shows Count of BOM Not Posted in each month like displayed in below. However, when I filter the data of BOM Status as of 31 Aug, 2021 with BOM not posted all other column reflect same count.

Region	As of 30th June, 2021	As of 31st July, 2021	As of 31st August, 2021
Abottabad	21	20	0
Bahawalpur	2	2	1
Bannu	27	22	22
D.G.Khan	24	24	24
Faisalabad	8	8	8
Gilgit	18	18	8
Gujranwala	9	7	2
Gujrat	10	9	9
Gwadar	17	13	13
Hyderabad	8	6	0
Islamabad	10	9	9
Jhang	19	19	7
Jhelum	26	24	24
Karachi South	2	1	0
Karachi West	1	0	0
Lahore Central	1	0	0
Lahore East	2	2	0
Larkana	6	6	1
Mansehra	16	16	16
Mardan	29	23	20
Mianwali	12	12	11
Mirpur Ak	32	32	32
Mirpur Khas	3	1	0
Multan	10	10	0
Muzaffarabad AK	10	10	10
Peshawar	11	8	3
Quetta	7	8	7
Rawalakot	21	20	19
Rawalpindi	14	11	11
Sahiwal	12	11	6
Sargodha	9	7	7
Sheikhupura	6	5	5
Sialkot	2	2	2
sibi	7	6	6
Sukkur	2	1	1
Swat	21	13	13
Vehari	14	14	14
Grand Total	449	400	311

 

[Chihiro]

You should flatten out the table. You will not get correct result when you try to filter multiple columns in value field.

Load data to Power Query. Then Unpivot Date columns. I'd also recommend transforming date string to actual date value after that.
Then load the result to data model and create pivot based on it.

 

[shaikhrulez]

Thanks @Chihiro for responding. Unable to understand what you said, could you please a tutorial or web link explaining that in detail.

Thanks.

 

[Chihiro]

Unpivot columns (Power Query) (microsoft.com)

 

[shaikhrulez]

I unpivot columns through Power Query as you have suggested, but still unable to make Pivot Table that looks like the one I posted in my first post.

Could you please look into the file and provide a solution.

Thanks.

 

[Chihiro]

You individually unpivoted date column. Instead you should select all date columns and unpivot at once.

Ex:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Region", "Branch Category"}, "Attribute", "Value"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByPositions({0, 17}, false), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Position",{"Attribute.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.2", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Region", type text}, {"Branch Category", type text}, {"Value", type text}})
in
    #"Changed Type"

Then you add fields to pivot and add a measure for counting.
Ex:
=CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Value]="BOM Not Posted"))

See attached. Sheet3 has the pivot.