Forum Discussion
Check if sliced value is higher than total average value
Hi Anonymous
I imported that data and set up the following example based on New Applications.
Let me know if you still have the same problem. If so, please show your expected results for New Applications by Store.
pbix: Pct above Avg 1.pbix
Solution SageI guess I'll need more than 2 lines of sample data.
Also, please show your expected results from the sample data supplied.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Apologies, here's some of the actual data from the Excel document that's being used.
| Store Code | Telesales Code | Store | New Applications | New Application £ | Softsearch Decline | Softsearch Accept | Softsearch Accept Rate | Dropouts | Dropout % | Full Applications | Total (Full) Accepts | Total (Full) Accepts £ | Full Auto Decline | Full Auto Decline % | Referred | Referred % | Undecisioned | Full Accept Rate | OCR | Written | Written £ |
| 1111 | 2111 | A | 130 | £720,075 | 16 | 114 | 87.69% | 9 | 7.89% | 105 | 94 | £580,976 | 0 | 0.00% | 16 | 15.24% | 7 | 95.92% | 93 | 93 | £574,582 |
| 1112 | 2112 | B | 15 | £78,517 | 1 | 14 | 93.33% | 0 | 0.00% | 14 | 14 | £78,517 | 0 | 0.00% | 0 | 0.00% | 0 | 100.00% | 14 | 14 | £78,517 |
| 1113 | 2113 | C | 43 | £146,364 | 11 | 32 | 74.42% | 5 | 15.63% | 27 | 21 | £87,996 | 0 | 0.00% | 8 | 29.63% | 4 | 91.30% | 20 | 20 | £86,463 |
| 1114 | 2114 | D | 98 | £360,270 | 29 | 69 | 70.41% | 6 | 8.70% | 63 | 61 | £308,371 | 0 | 0.00% | 11 | 17.46% | 2 | 100.00% | 60 | 59 | £293,388 |
| 1115 | 2115 | E | 20 | £84,137 | 3 | 17 | 85.00% | 1 | 5.88% | 16 | 15 | £78,628 | 0 | 0.00% | 1 | 6.25% | 1 | 100.00% | 15 | 15 | £78,628 |
| 1116 | 2116 | F | 44 | £169,891 | 9 | 35 | 79.55% | 2 | 5.71% | 33 | 32 | £162,595 | 0 | 0.00% | 3 | 9.09% | 1 | 100.00% | 32 | 32 | £162,595 |
| 1117 | 2117 | G | 68 | £276,231 | 10 | 58 | 85.29% | 5 | 8.62% | 53 | 50 | £246,501 | 0 | 0.00% | 10 | 18.87% | 3 | 100.00% | 49 | 49 | £245,992 |
| 1118 | 2118 | H | 47 | £264,267 | 2 | 45 | 95.74% | 4 | 8.89% | 41 | 41 | £242,044 | 0 | 0.00% | 3 | 7.32% | 0 | 100.00% | 40 | 39 | £229,034 |
| 1119 | 2119 | I | 66 | £261,523 | 11 | 55 | 83.33% | 9 | 16.36% | 46 | 44 | £211,016 | 0 | 0.00% | 8 | 17.39% | 2 | 100.00% | 43 | 43 | £208,442 |
| 1120 | 2120 | J | 73 | £315,328 | 18 | 55 | 75.34% | 7 | 12.73% | 48 | 48 | £257,838 | 0 | 0.00% | 4 | 8.33% | 0 | 100.00% | 46 | 46 | £255,842 |
| 1121 | 2121 | K | 23 | £87,079 | 5 | 18 | 78.26% | 1 | 5.56% | 17 | 17 | £82,398 | 0 | 0.00% | 0 | 0.00% | 0 | 100.00% | 17 | 16 | £77,080 |
| 1122 | 2122 | L | 54 | £186,737 | 8 | 46 | 85.19% | 7 | 15.22% | 39 | 39 | £165,200 | 0 | 0.00% | 4 | 10.26% | 0 | 100.00% | 38 | 38 | £163,658 |
| 1123 | 2123 | M | 41 | £191,526 | 7 | 34 | 82.93% | 6 | 17.65% | 28 | 28 | £136,626 | 0 | 0.00% | 2 | 7.14% | 0 | 100.00% | 24 | 24 | £122,073 |
- grantsamborn
Hi Anonymous
I imported that data and set up the following example based on New Applications.
Let me know if you still have the same problem. If so, please show your expected results for New Applications by Store.
pbix: Pct above Avg 1.pbix
That seems to have done it! Thank you for that.
The only thing I can't seem to do now is do the same for a measure I created called AOV (Average order value) that divides the full accepts £ by the full accepts.- grantsamborn
Would this help?
AOV = DIVIDE( SUM( 'DataTable'[Total (Full) Accepts Amt] ), SUM( 'DataTable'[Total (Full) Accepts] ) )
- Ashish_Mathur
Super User
Hi,
Based on the table that you have shared, show the expected result very clearly.
