Monthly Vs Quarterly

arnabmit

New Member
Joined
Mar 28, 2009
Messages
21
Office Version
  1. 2019
  2. 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.
 

Some videos you may like

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.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
870
Office Version
  1. 365
Platform
  1. Windows
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))
 

arnabmit

New Member
Joined
Mar 28, 2009
Messages
21
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
870
Office Version
  1. 365
Platform
  1. Windows
I don't think it can be done through Slicer. Instead if you want it can be done through Formula.
 

arnabmit

New Member
Joined
Mar 28, 2009
Messages
21
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I don't think it can be done through Slicer. Instead if you want it can be done through Formula.
It would be great if you can help me implement it using M code.
 

Bo_Ry

Board Regular
Joined
Oct 27, 2018
Messages
112
Office Version
  1. 365
Platform
  1. Windows
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
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
502
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

arnabmit

New Member
Joined
Mar 28, 2009
Messages
21
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

Bo_Ry

Board Regular
Joined
Oct 27, 2018
Messages
112
Office Version
  1. 365
Platform
  1. Windows
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
 

arnabmit

New Member
Joined
Mar 28, 2009
Messages
21
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,738
Messages
5,524,543
Members
409,583
Latest member
gkarthick

This Week's Hot Topics

Top