Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
Solved! Go to 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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |