• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot is not calculating correct month wise count

shaikhrulez

Active Member
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.

RegionAs of 30th June, 2021As of 31st July, 2021As of 31st August, 2021
Abottabad21200
Bahawalpur221
Bannu272222
D.G.Khan242424
Faisalabad888
Gilgit18188
Gujranwala972
Gujrat1099
Gwadar171313
Hyderabad860
Islamabad1099
Jhang19197
Jhelum262424
Karachi South210
Karachi West100
Lahore Central100
Lahore East220
Larkana661
Mansehra161616
Mardan292320
Mianwali121211
Mirpur Ak323232
Mirpur Khas310
Multan10100
Muzaffarabad AK101010
Peshawar1183
Quetta787
Rawalakot212019
Rawalpindi141111
Sahiwal12116
Sargodha977
Sheikhupura655
Sialkot222
sibi766
Sukkur211
Swat211313
Vehari141414
Grand Total449400311
 

Attachments

  • For Chandoo.xlsx
    50.3 KB · Views: 6
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.
 
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.
 

Attachments

  • For Chandoo - 2.xlsx
    70.7 KB · Views: 1
You individually unpivoted date column. Instead you should select all date columns and unpivot at once.

Ex:
Code:
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.
 

Attachments

  • For Chandoo.xlsx
    216.8 KB · Views: 1
Back
Top