Invoice Drop Size with Slicer

[danb]

Power BI Community, 

I am running into a quandry in trying to calculate an averasge drop size of a list of invoices for stores. The issue is that i want to have a slicer that will allow me to suppress invoices below a certain total case threshold.

Example (Please see attached mockup in excel):

In the table below, I have the base data. I also want to layer in a "Drop Size Threshold" slicer (highlighted in yellow) that will allow the user to select either 0,10 or 25. The need would be for whatever the value that is selected in the Drop Size Threshold would then suppress any invoices that are less then the selected value. The picture above has the expected answers. 

The calculations for the two results are as follows:

• Average Drop Size = Average of cases that are >= the Drop Size Threshold
• # of Drops = Count of the invoices that have total cases >= the Drop Size Threshold 

Here is the base data

Region	Invoice #	InvoiceDate	Store	Total Cases
Billings, MT	1009	3/19/2019	A	7
Billings, MT	1019	3/19/2019	A	141
Billings, MT	1024	3/20/2019	A	0
Billings, MT	1031	3/22/2019	A	1
Billings, MT	1047	3/22/2019	A	142
Billings, MT	1059	3/26/2019	A	1
Billings, MT	1069	3/26/2019	A	140
Billings, MT	1083	3/28/2019	A	3
Billings, MT	1103	3/29/2019	A	134
Billings, MT	1104	3/29/2019	A	2
Billings, MT	1117	4/2/2019	A	-2
Billings, MT	1118	4/2/2019	A	-1
Billings, MT	1124	4/2/2019	A	118
Billings, MT	1146	4/5/2019	A	126
Billings, MT	1147	4/5/2019	A	1
Billings, MT	1150	4/6/2019	A	1
Billings, MT	1169	4/9/2019	A	120
Billings, MT	1178	4/11/2019	A	1
Billings, MT	1197	4/12/2019	A	140
Billings, MT	1198	4/12/2019	A	1
Billings, MT	1013	3/19/2019	B	48
Billings, MT	1014	3/19/2019	B	2
Billings, MT	1040	3/22/2019	B	23
Billings, MT	1065	3/26/2019	B	37
Billings, MT	1097	3/29/2019	B	39
Billings, MT	1120	4/2/2019	B	62
Billings, MT	1141	4/5/2019	B	35
Billings, MT	1163	4/9/2019	B	48
Billings, MT	1189	4/12/2019	B	27
Billings, MT	1190	4/12/2019	B	1
Billings, MT	1015	3/19/2019	C	111
Billings, MT	1016	3/19/2019	C	1
Billings, MT	1043	3/22/2019	C	57
Billings, MT	1044	3/22/2019	C	1
Billings, MT	1066	3/26/2019	C	75
Billings, MT	1074	3/27/2019	C	1
Billings, MT	1100	3/29/2019	C	66
Billings, MT	1101	3/29/2019	C	1
Billings, MT	1121	4/2/2019	C	109
Billings, MT	1143	4/5/2019	C	32
Billings, MT	1165	4/9/2019	C	101
Billings, MT	1192	4/12/2019	C	45
Billings, MT	1003	3/18/2019	E	51
Billings, MT	1020	3/20/2019	E	-1
Billings, MT	1021	3/20/2019	E	-2
Billings, MT	1032	3/22/2019	E	87
Billings, MT	1053	3/25/2019	E	49
Billings, MT	1089	3/29/2019	E	74
Billings, MT	1106	3/30/2019	E	-1
Billings, MT	1109	4/1/2019	E	58
Billings, MT	1134	4/5/2019	E	83
Billings, MT	1151	4/6/2019	E	4
Billings, MT	1153	4/8/2019	E	60
Billings, MT	1181	4/12/2019	E	-1
Billings, MT	1182	4/12/2019	E	71
Billings, MT	1183	4/12/2019	E	1
Billings, MT	1203	4/15/2019	E	59

Thank you!!!

Dan

[v-lili6-msft · Accepted solution]

hi, @danb 

After my research, you could try this way as below:

Step1:

Use a What if parameter to add a "Drop Size Threshold" slicer

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

or you could custom a "Drop Size Threshold" slicer table and add a Parameter Value measure

Parameter Value = SELECTEDVALUE('Parameter'[Parameter])

Step2:

Then add two measure

Average Drop Size = CALCULATE(AVERAGE('Table'[Total Cases]),FILTER('Table','Table'[Total Cases]>=[Parameter Value]))

# of Drops = CALCULATE(COUNTA('Table'[Invoice #]),FILTER('Table','Table'[Total Cases]>=[Parameter Value]))

Result:

and here is a pbix file, please try it.

Best Regards,

Lin

[danb]

@v-lili6-msft  - That did it! Thank you for the detailed and easy to follow solution!