Check if sliced value is higher than total average value

[DanB92]

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.

[grantsamborn]

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

[DanB92]

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%?

[grantsamborn]

I 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/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[DanB92]

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

[Ashish_Mathur]

Hi,

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

[grantsamborn · Accepted solution]

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

[DanB92]

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] )
    )