Monthly Vs Quarterly

arnabmit

New Member
Joined
Mar 28, 2009
Messages
27
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

I have 3 tables, one with sales figures, another a HeadCount table, another a calendar table.

Calendar:
Date Month Quarter
4/1/19 Apr Q1
4/2/19 Apr Q1
4/3/19 Apr Q1
...


SalesPerson HeadCount
Month HC
Apr 12
May 10
Jun 9
Jul 10
...


Sales:
EmpID Date Qty_Sold
1234 4/1/19 3
2345 4/1/19 2
3456 4/1/19 4
...

I want to find out total SumOfQty_Sales/SalesPersons to get AvgQtySoldPerPerson month wise.

The place where I am getting stuck is that when I select the Q1 in the slicer, instead of month.

I want it to give me total Qty_Sold for the quarter / HC for Jun (quarter closing HC). Instead it is giving me total Qty_Sold / total HC.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
This is a simple example of your result sought. I want your report layout picture so that i can understand where is the probelm in Slicer???

aug20 maa bhagwati.xlsx
ABCDEFGHIJKL
1MonthHCSales
2April12EmployeeIDDateQuantity Sold
3May10123401-04-20193
4June9234502-04-20192
5July10345603-04-20193
6111101-05-20191
7222202-05-201932
8333303-05-20191
9
10
11
12
13MonthWise Average Quantity Sold
14
15April0.67
16May3.40
17June-
18July-
19
20
Sheet2
Cell Formulas
RangeFormula
B15:B18B15=SUM(IF(TEXT($H$3:$H$8,"mmmM")=A15,$I$3:$I$8))/SUM(IF($A$15:$A$18=A2,$B$2:$B$5))
 
Upvote 0
This is a simple example of your result sought. I want your report layout picture so that i can understand where is the probelm in Slicer???

Hi,

These are the tables and result sample I am working with. The date slicer I will be using is attached as an image

Date Test.xlsx
ABCDEFGHIJK
1DateMonthQtrMonthHCEmpIDDateQty_Sold
21-Apr-19AprQ1Apr1212341-Apr-195
32-Apr-19AprQ1May1023455-Apr-196
43-Apr-19AprQ1Jun934565-May-197
54-Apr-19AprQ1Jul1045676-Jun-194
65-Apr-19AprQ1Aug1156787-Jul-193
7Sep1267898-Aug-199
81-May-19MayQ1Oct1078909-Sep-195
91-Jun-19JunQ1Nov9890110-Oct-194
101-Jul-19JulQ2Dec10901211-Nov-198
111-Aug-19AugQ212344-Apr-196
121-Sep-19SepQ223454-May-192
131-Oct-19OctQ334564-Jun-194
141-Nov-19NovQ345674-Jul-196
151-Dec-19DecQ356784-Aug-195
1667894-Sep-197
17Result78904-Oct-199
1889014-Nov-193
19PeriodHCQtyQty/HC90124-Dec-191
20Apr12221.8123425-Apr-195
21May10111.1234525-May-192
22Jun9121.3345625-Jun-194
23Q19455.0456725-Jul-196
24Jul10151.5567825-Aug-199
25Aug11232.1678925-Sep-197
26Sep12191.6789025-Oct-198
27Q212574.8890125-Nov-195
28901225-Dec-198
29
Sheet1
Cell Formulas
RangeFormula
B8:B15,B2:B6B2=TEXT(A2,"mmm")
C8:C15,C2:C6C2=CHOOSE(ROUNDUP(MONTH(A2)/3,0),"Q4","Q1","Q2","Q3")
C23,C27C23=C22
D23,D27D23=SUM(D20:D22)
E20:E27E20=D20/C20
 

Attachments

  • DateSlicer.PNG
    DateSlicer.PNG
    5.2 KB · Views: 6
Upvote 0
I don't think it can be done through Slicer. Instead if you want it can be done through Formula.
 
Upvote 0
Create Table HC and Qty
Then

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Qty"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    AddedMonthName = Table.AddColumn(#"Changed Type", "Period", each Date.ToText([Date],"MMM")),
    AddedQuarter = Table.AddColumn(AddedMonthName, "Quarter", each Number.ToText(Number.Mod(3+Number.IntegerDivide(Date.Month([Date])-1,3),4)+1,"\Q0")),
    #"Grouped Rows" = Table.Group(AddedQuarter, {"Quarter", "Period"}, {{"Qty", each List.Sum([Qty_Sold]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Period"}, Excel.CurrentWorkbook(){[Name="HC"]}[Content], {"Month"}, "Grouped", JoinKind.LeftOuter),
    #"Expanded Grouped" = Table.ExpandTableColumn(#"Merged Queries", "Grouped", {"HC"}, {"HC"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Grouped", {"Quarter"}, {{"A", each Table.InsertRows(_,Table.RowCount(_),{[Quarter = null,Period = [Quarter]{0},Qty = List.Sum([Qty]), HC =[HC]{2} ]} )}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows1",{"Quarter"}),
    #"Expanded A" = Table.ExpandTableColumn(#"Removed Columns", "A", {"Period", "HC","Qty"}),
    #"AddedQty/HC" = Table.AddColumn(#"Expanded A", "Qty/HC", each [Qty]/[HC]),
    #"Changed Type1" = Table.TransformColumnTypes(#"AddedQty/HC",{{"Period", type text}, {"Qty", Int64.Type}, {"HC", Int64.Type}, {"Qty/HC", type number}})
in
    #"Changed Type1"


Book1
ABCDEFGHIJKLMNO
1DateMonthQtrMonthHCEmpIDDateQty_SoldPeriodHCQtyQty/HC
243556AprQ1Apr12123401-Apr-195Apr12221.833333
343557AprQ1May10234505-Apr-196May10111.1
443558AprQ1Jun9345605-May-197Jun9121.333333
543559AprQ1Jul10456706-Jun-194Q19455
643560AprQ1Aug11567807-Jul-193Jul10151.5
7Sep12678908-Aug-199Aug11232.090909
843586MayQ1Oct10789009-Sep-195Sep12191.583333
943617JunQ1Nov9890110-Oct-194Q212574.75
1043647JulQ2Dec10901211-Nov-198Oct10212.1
1143678AugQ2123404-Apr-196Nov9161.777778
1243709SepQ2234504-May-192Dec1090.9
1343739OctQ3345604-Jun-194Q310464.6
1443770NovQ3456704-Jul-196
1543800DecQ3567804-Aug-195
16678904-Sep-197
17Result789004-Oct-199
18890104-Nov-193
19PeriodHCQtyQty/HC901204-Dec-191
20Apr12221.833333333123425-Apr-195
21May10111.1234525-May-192
22Jun9121.333333333345625-Jun-194
23Q19455456725-Jul-196
24Jul10151.5567825-Aug-199
25Aug11232.090909091678925-Sep-197
26Sep12191.583333333789025-Oct-198
27Q212574.75890125-Nov-195
28901225-Dec-198
Sheet1
 
Upvote 0
Create two tables: HC and Sales.
PQ:
1599998844999.png

VBA Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table42"]}[Content],
    TransDate = Table.RemoveColumns(
                    Table.SplitColumn(Source,"Date",each 
                        {
                            Date.ToText(DateTime.Date(_),"MMM","en-US"),
                            Number.ToText(1+Number.IntegerDivide(Date.Month(_)-1,3),"\Q0")
                        },{"Period","Q"}),"EmpID"),
    res = Table.Combine(Table.Group(TransDate,"Q",{"New",(y)=> 
            let
            tbl=Table.SplitColumn(Table.Group(y,"Period",{"n",(x)=>x}),"n",(z)=>
                let
                    a=HC{[Month=z{0}[Period]]}[HC],
                    b=List.Sum(z[Qty_Sold]),
                    c=b/a
                in
                    {a,b,c},
                {"HC","Qty","Qty/HC"}
                             ),
            Out=Table.InsertRows(tbl,Table.RowCount(tbl),
                            {[
                                Period=y{0}[Q],
                                HC=List.Last(tbl[HC]),
                                Qty=List.Sum(tbl[Qty]),
                                #"Qty/HC"=Qty/HC
                             ]})
            in
                Out}                             
                     )[New])
in
    res
Result Table:
1599998995179.png
 
Upvote 0
Thanks for the suggestions, however, I cannot use VBA.

Can this be done via DAX measures or calculated columns, as I am eventually going to use this data in Power BI.

Also, I cannot hard code them as they will have to calculate different results based on filter context from other slicers.
 
Upvote 0
Add table HC and Qty to Data model
HC Table
Add Calculate Column: Date
=DATEVALUE("1-"&HC[Month]&"-2019")

Then Create New Date table from Ribbon design

Edit Calculate Column: Month to
=FORMAT([Date],"MMM")
Add Calculate Column: Quarter
="Q"&mod(int(('Calendar'[Month Number]-1)/3)-1,4)+1

Then Create relationships From Calendar Date to both HC and Qty Date.
Please see the direction of the relationships.
Relation.png

Then Insert Pivot Table
Measure:
Total Qty
=Sum(Qty[Qty_Sold])
Total HC
=CALCULATE( Sum(HC[HC]), FILTER(HC, HC[Date] = MAX(HC[Date])) )
Qty/HC
=DIVIDE([Total Qty],[Total HC])


Monthly Vs Quarterly.xlsx
ABCDEFGHIJKLMNOP
1DateMonthQtrMonthHCEmpIDDateQty_SoldPeriodHCQtyQty/HC
243556AprQ1Apr12123401-Apr-195Apr12221.8333
343557AprQ1May10234505-Apr-196May10111.1
443558AprQ1Jun9345605-May-197Jun9121.3333
543559AprQ1Jul10456706-Jun-194Q19455
643560AprQ1Aug11567807-Jul-193Jul10151.5
7Sep12678908-Aug-199Aug11232.0909
843586MayQ1Oct10789009-Sep-195Sep12191.5833
943617JunQ1Nov9890110-Oct-194Q212574.75
1043647JulQ2Dec10901211-Nov-198Oct10212.1
1143678AugQ2123404-Apr-196Nov9161.7778
1243709SepQ2234504-May-192Dec1090.9
1343739OctQ3345604-Jun-194Q310464.6
1443770NovQ3456704-Jul-196
1543800DecQ3567804-Aug-195
16678904-Sep-197
17Result789004-Oct-199
18890104-Nov-193
19PeriodHCQtyQty/HC901204-Dec-191QuarterMonthTotal HCTotal QtyQty/HC
20Apr12221.833333333123425-Apr-195Q1Apr12221.83
21May10111.1234525-May-192May10111.10
22Jun9121.333333333345625-Jun-194Jun9121.33
23Q19455456725-Jul-196Q1 Total9455.00
24Jul10151.5567825-Aug-199Q2Jul10151.50
25Aug11232.090909091678925-Sep-197Aug11232.09
26Sep12191.583333333789025-Oct-198Sep12191.58
27Q212574.75890125-Nov-195Q2 Total12574.75
28901225-Dec-198
Sheet1
 
Upvote 0
Solution
Add table HC and Qty to Data model
HC Table
Add Calculate Column: Date
=DATEVALUE("1-"&HC[Month]&"-2019")

Then Create New Date table from Ribbon design

Edit Calculate Column: Month to
=FORMAT([Date],"MMM")
Add Calculate Column: Quarter
="Q"&mod(int(('Calendar'[Month Number]-1)/3)-1,4)+1

Then Create relationships From Calendar Date to both HC and Qty Date.
Please see the direction of the relationships.
View attachment 22287
Then Insert Pivot Table
Measure:
Total Qty
=Sum(Qty[Qty_Sold])
Total HC
=CALCULATE( Sum(HC[HC]), FILTER(HC, HC[Date] = MAX(HC[Date])) )
Qty/HC
=DIVIDE([Total Qty],[Total HC])


Thank you so much! This makes perfect sense! :)
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top