Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
This is what the filter looks like...
And this is what I want to get to.
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
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.
Hmmm...That hasn't worked. I get this error as it's a measure not a column.
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
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?!
Test6 = ('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
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)
This is what the filter looks like...
And this is what I want to get to.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |