cancel
Showing results for
Did you mean:

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.

Regular Visitor

## Check if sliced value is higher than total average value

Hi all,

I'm probably going to make this sound more complicated than it is but what I'm looking to do is see if the slicer value is higher or lower than the average of a column.

For example:

 Shop Average Order Value Applications Applications £ 1123 £1,111 5 £3,409 1124 £2,222 9 £6,958

I've got a slicer set up to select the shop number and it will then show the applications and applications £ on cards underneath. What I want to add in is the % above average of the total of applications for example so the average of the applications would be 7, the shop 1123 has done 5 so I'm looking to work out the % difference between that.

I want to do this for each card that I've got setup on the dashboard.

All my data is in one table as well.

1 ACCEPTED SOLUTION
Solution Sage

Hi @DanB92

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

8 REPLIES 8
Solution Sage

Hi @DanB92

Would something like this help?

``````Pct above Avg =
DIVIDE(
SUM( 'DataTable'[Applications] ),
CALCULATE(
AVERAGE( 'DataTable'[Applications] ),
ALL( 'DataTable'[Shop] )
)
) - 1``````

pbix: Pct above Avg.pbix

Regular Visitor

Thanks for that. It's giving me a value but it seems to be working it out wrong somewhere.

One column I have in Excel averages out at 5 and the slicer shows 16 but the DAX code above is showing it as 244.23% above when I think it should be 320%?

Solution Sage
Regular Visitor

Apologies, here's some of the actual data from the Excel document that's being used.

 StoreCode TelesalesCode Store NewApplications NewApplication £ SoftsearchDecline SoftsearchAccept SoftsearchAcceptRate Dropouts Dropout% FullApplications Total(Full)Accepts Total (Full)Accepts £ FullAutoDecline FullAutoDecline% Referred Referred% Undecisioned FullAcceptRate 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
Super User

Hi,

Based on the table that you have shared, show the expected result very clearly.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Sage

Hi @DanB92

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

Regular Visitor

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.

Solution Sage

Would this help?

``````AOV =
DIVIDE(
SUM( 'DataTable'[Total (Full) Accepts Amt] ),
SUM( 'DataTable'[Total (Full) Accepts] )
)``````

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors