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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Who has more or less than the average?

Morning everyone,

 

I am trying to highlight potential issues to my users - I have the overall number of cases due in the next week by user but what I actually want is to highlight who has more and less than the average in the hopes my users will move cases to those with less work.

 

My main FACT table is cases with measures to calculate Number of active cases, resolved cases, resolve by date etc, a Users table with user name and other details and a DimDate table with every date and columns so we can highlight whether something is yesterday/today/tomorrow/this week etc.

 

This is what I have to start with...(removed names for privacy reasons)

Capture2.PNG

 

This is what the filter looks like...

 

Capture.PNG

 

And this is what I want to get to.

 

Capture.PNG

Please can someone assist me with the DAX function for this?  I have tried SUMMARIZE and AVERAGEX but I don't really know what I'm doing!

 

Many thanks for any help

Natalie

xXx

 

6 REPLIES 6
Greg_Deckler
Super User
Super User

If you have a measure [Total Active Cases by Resolve Date], then you should be able to do a SUM either on that measure or as part of the same formula you used to derive that measure. Then you should be able to divide it by the number of people. Something like:

 

MyMeasure = SUM([Total Active Cases by Resolve Date]) / DISTINCTCOUNT(People[fullname])

That should give you your average.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hmmm...That hasn't worked.  I get this error as it's a measure not a column.

 

Capture.PNG

 

 

Hi Pinkybloo,

So what is your current situation?

 

Based on my understanding, you have the value of "overall number of cases due in the next week", is this a measure?

For the cases, is the calculating only involved the active cases?

If this is the point, then we could writing filters within the measure to only count the active cases, then use that number in the following calculation.

For the average amount,  we could just calculate the total number of the active cases, with all fact table calculated (well, filters differ depending on whether you would like to have Slicer Visual to work with, we may consider use allselected() instead of all() function if slicer is considered), then divided by the distinctcount of the people value, this should be the average.

As I didn't know the actual table is, here I would offer some formula sample:

TotalActiveCases := Sumx(“table expression (we should use filter here to filter the fact table)”, ‘fact’[caseID]),

Average := [TotalActiveCases]/DistinctCount('users'[username])

If this is still not working, then please share us some data sample here, for further investigation.

Regards

 

Anonymous
Not applicable

Hi,

 

Thanks for your response.  My current status is still confused!  I have tried your suggestion but it doesn't show what I'd expect, it says infinity?!

 

Capture.PNGTest6 = ('Cases'[Total Active Cases by Resolve Date])/DISTINCTCOUNT(SystemUser[fullname])

 

I have a measure called Total active cases by resolve date which has user relationships in it because my dates table has a relationship on created date but for this I need to activate my join between the two tables to use resolve by date

CALCULATE(IF(ISBLANK(SUM('Cases'[TotalActiveCases])),0,SUM('Cases'[TotalActiveCases])),USERELATIONSHIP('Cases'[resolveby],Dates[FullDateAlternateKey]))

 

I use a filter from my dates table called Next7Days to highlight those due in the next week so the next 7 days is always dynamic.

 

Of the total cases this week there are 482 and 41 people, so I want to create a measure which says the average is 19 pp.  Then I want to use this measure to highlight who has more or less than that average figure for workload purposes.

 

Is this enough info t be able to help?

 

Many thanks

Hi Pinkybloo,

 

As you could see from your image, the value of [Total Active Cases by Resolve Date] would change based on the User name in the table visual, what I suggest to put here is the total cases, which would need to calculate a new total number with the All function, ingoring the filter context.

We need another neasure to calculate the total cases, not the same [Total Active Cases by Resolve Date] measure.

We need to ingore the User name in the calculation of the total cases, in order to get the correct total number in total cases calculation.

Would it be available for you to share some data samples? Only with the formula I am not able to suggest a suitable formula on that.

Regards

 

Anonymous
Not applicable

Morning everyone,

 

I am trying to highlight potential issues to my users - I have the overall number of cases due in the next week by user but what I actually want is to highlight who has more and less than the average in the hopes my users will move cases to those with less work.

 

My main FACT table is cases with measures to calculate Number of active cases, resolved cases, resolve by date etc, a Users table with user name and other details and a DimDate table with every date and columns so we can highlight whether something is yesterday/today/tomorrow/this week etc.

 

This is what I have to start with...(removed names for privacy reasons)

Capture2.PNG

 

This is what the filter looks like...

 

Capture.PNG

 

And this is what I want to get to.

 

Capture.PNG

Please can someone assist me with the DAX function for this?  I have tried SUMMARIZE and AVERAGEX but I don't really know what I'm doing!

 

Many thanks for any help

Natalie

xXx

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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