Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DanB92
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:

ShopAverage Order ValueApplicationsApplications £
1123£1,1115£3,409
1124£2,2229£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

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

 

View solution in original post

8 REPLIES 8
grantsamborn
Solution Sage
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

 

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

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

 

Store
Code
Telesales
Code
StoreNew
Applications
New
Application £
Softsearch
Decline
Softsearch
Accept
Softsearch
Accept
Rate
DropoutsDropout
%
Full
Applications
Total
(Full)
Accepts
Total (Full)
Accepts £
Full
Auto
Decline
Full
Auto
Decline
%
ReferredReferred
%
UndecisionedFull
Accept
Rate
OCRWrittenWritten £
11112111A130£720,0751611487.69%97.89%10594£580,97600.00%1615.24%795.92%9393£574,582
11122112B15£78,51711493.33%00.00%1414£78,51700.00%00.00%0100.00%1414£78,517
11132113C43£146,364113274.42%515.63%2721£87,99600.00%829.63%491.30%2020£86,463
11142114D98£360,270296970.41%68.70%6361£308,37100.00%1117.46%2100.00%6059£293,388
11152115E20£84,13731785.00%15.88%1615£78,62800.00%16.25%1100.00%1515£78,628
11162116F44£169,89193579.55%25.71%3332£162,59500.00%39.09%1100.00%3232£162,595
11172117G68£276,231105885.29%58.62%5350£246,50100.00%1018.87%3100.00%4949£245,992
11182118H47£264,26724595.74%48.89%4141£242,04400.00%37.32%0100.00%4039£229,034
11192119I66£261,523115583.33%916.36%4644£211,01600.00%817.39%2100.00%4343£208,442
11202120J73£315,328185575.34%712.73%4848£257,83800.00%48.33%0100.00%4646£255,842
11212121K23£87,07951878.26%15.56%1717£82,39800.00%00.00%0100.00%1716£77,080
11222122L54£186,73784685.19%715.22%3939£165,20000.00%410.26%0100.00%3838£163,658
11232123M41£191,52673482.93%617.65%2828£136,62600.00%27.14%0100.00%2424£122,073

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

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.

Would this help?

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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