Forum Discussion
Invoice Drop Size with Slicer
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
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
2 Replies
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
v-lili6-msft - That did it! Thank you for the detailed and easy to follow solution!
