Forum Discussion
Solved
Apply Pivot Table on selected items with addition filter of billed in current quarter only
1. Below is image of data set on which I have to apply pivot. For reference I gave 3 sets, actually it can go upto 5000 2. Conditions are: a) Customer name, manufacturer & item name should be sam...
Let assume you your Excel version/platform supports data model. Creating PivotTable you need to add data to data model:
Right click on table name In PivotTable Fields pane and Add measure
Create DAX measure in opened window
Use this measure in PivotTable for aggregation
This is intended to achieve the same result using a 365 formula
= LET(
inQtr, COUNTIFS(
Table1[Customer Name], Table1[Customer Name],
Table1[Manufacturer], Table1[Manufacturer],
Table1[Item], Table1[Item],
Table1[Bill date], ">=" & DATE(2022,7,1),
Table1[Bill date], "<=" & DATE(2022,9,31)
),
total, SUMIFS(Table1[Amount],
Table1[Customer Name], Table1[Customer Name],
Table1[Manufacturer], Table1[Manufacturer],
Table1[Item], Table1[Item]
),
FILTER(Table1, (inQtr>0)*(total>=3000))
)Without LET and FILTER you would need helper columns (or Power Query of course)
So, we have full set of solutions except one for legacy Excel. Most probably it could be done with AGGREGATE(), but I'm lazy to generate it now.