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
xiumi_hou
Post Partisan
Post Partisan

Question Regarding calculate group average

Dear allI was trying to calculate different indicators' average by group. The avergae is total number of cases/total number of people. When I put my dax function as below: 

Count_Average_Closed_Cases =
CALCULATE(
DIVIDE(count(f_Cases[CaseNumber]),count(d_users[id])),
f_Cases[Status] = "Closed",
USERELATIONSHIP(f_Cases[Closed_date_c],d_date[Date])
)
The result show exactly not the group average, but the single people's number based on my filter. Any suggestion? Thanks in advance. 
4 REPLIES 4
parry2k
Super User
Super User

@xiumi_hou can you share the sample data and expected result and also how you want it to behave when you filter the data?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi Parry, Thanks for asking.

Actually I would like to separate into two parts: 

1.  I have Five scliers: Year/Month/Group/User Name/Indicators

    The goal is to monitor for a single month, what is the perforamce for user A's indicator A (I want to use actual value compare with group average as well as the target value).

    I have created several measure for the actual value of each indicator, however, I do not know how to link the target value and how to calculate the average within group and link back to the actual value? below is the sample data:

Question 2.JPG

 

 

 

 

 

 

 

 

 

2. The second condition is exactly same with the previous one, the quesiton is when I calculate average, I use function: 

Count_Average_Closed_Cases =
CALCULATE(
DIVIDE(count(Indicator ID),count(d_users[USER id])),
f_Cases[Status] = "Closed",
USERELATIONSHIP(f_Cases[Closed_date_c],d_date[Date])
)
 
The question is I tried to sum up to count the number of single indicator and divide by the number of user to calculate the group average, but i do not know how to seperate by group? And when I select a fitler, the average is exactly the same as the person I selected.. Thanks!! @parry2k 

@xiumi_hou im not fully clear about your requirement. Could you plese share data in excel file and put expected result in a sheet explaining calculation and will get you the solution.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hello Parry,

 

Please find the attached excel document. I want to create a chart with the actual value compare with average and target. For example, when I choose the year 2017,month Aug, Group A, User: Sample 1, Indicator: Assessment, I would like to see this users;s actual value(how many assessment held in that month) with target and group A's average in one chart.

Below is the sample data: 

https://1drv.ms/x/s!AlqPFuio7_SNielZyCarJMba9i1MfQ

 

Please let mw know if you need more information. Thanks!! @parry2k 

 

 

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.