cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Finding the average for a particular department

Hi,

I'm using PowerBI desktop trying to create a very simple visualisation. 

I have two columns of data:

Department List and Bid Approval Status.

The Department List has multiple duplicate entries in it, for example 10 x Computer Science, 5 x English etc,

The Bid Approval Status has multiple duplicate entries in it also which apply to the departments whether their bid has been successfull or not, for example Approved, Rejected, Withdrawn.

 

I want to show a visualisation that displays the average Bid Approval Status for each department. 

 

I've played about with the query editor and tried making custom columns and tried typing in formulas but it's a farcry from Excel so I'm struggling. I also abandoned the Query Editor and went straight into PowerBI Desktop and played around with the tools within and have failed.

 

Can someone tell me the easiest way to do this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Finding the average for a particular department

Hi @cuskav,

 

I am still a little confused. How to average strings (Stable, Low Risk). You can change the blue part to your table name.

Average =
COUNT ( Table1[ServiceStatus] ) / DISTINCTCOUNT ( Table1[ServiceStatus] )

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
Highlighted
Resolver II
Resolver II

Re: Finding the average for a particular department

Can you provide some sample data?

Highlighted
Regular Visitor

Re: Finding the average for a particular department

Yes:

 

fdg.PNG

The Title column holds the department and the ServiceStatus column holds Stable, Low Risk or High Risk.

Both columns hold duplicate values, this data was extracted over the course of 2 years so each duplicate is a monthly entry into a SharePoint list, fyi. The third column with the 0's is me just messing about!

 

Highlighted
Solution Sage
Solution Sage

Re: Finding the average for a particular department

@cuskav Have you tried the Group By option?

Highlighted
Microsoft
Microsoft

Re: Finding the average for a particular department

Hi @cuskav,

 

How to evaluate the average of the status? Did you mean: for example E: 1 "Stable" and 2 "High Risk", the average is (1 + 2)/2 = 1.5?

Title	ServiceStatus
E	Stable
E	High Risk
E	High Risk
G	Stable
G	Stable
G	Low Risk
G	Stable
S	Stable
S	Stable
S	Low Risk
S	Low Risk
S	Low Risk

If so, you could try a measure like this:

 

Average =
COUNT ( Table1[ServiceStatus] ) / DISTINCTCOUNT ( Table1[ServiceStatus] )

Finding the average for a particular department .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: Finding the average for a particular department

Hi Dale,

 

Thanks for you help so far.

 

I see how you have 3 visualisations (albeit they are tables). Can you tell me, for the top one table with the ServiceStatus, Title and Count of Title column headers, did you create a custom column using a DAX formula to find the Count of Title? If so how?

It seems I'm stuck at the first hurdle here. A lot of the tutorials presume people have used DAX before so I'm not getting much joy from the MS provided ones.

 

Ultimately I want to show a visualisation that shows the average status for each department. For example:

 

Service (Title)         ServiceStatus        

IT Security                  Stable

Mgt Info                     Stable

Server Provisioning    Low Risk

Highlighted
Microsoft
Microsoft

Re: Finding the average for a particular department

Hi @cuskav,

 

Drag the column into the "Values". Then you can get another column. Click the inverted triangle and you will find some out-of-box functions. Don't need to add a column or a measure. It's enough for some simple calculations.Finding the average for a particular department2 .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: Finding the average for a particular department

Thanks Dale - how would you create the second table? Using the formula you gave? Edit - just realised, drag the column again.

 

Highlighted
Microsoft
Microsoft

Re: Finding the average for a particular department

Hi @cuskav,

 

That's right. One question, how to get the result of yours if I have a dataset like this? I need the exact process.

Title    	ServiceStatus
IT Security	Stable
IT Security	Stable
IT Security	Stable
IT Security	Low Risk
IT Security	Low Risk
Mgt Info	Stable
Mgt Info	Low Risk

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: Finding the average for a particular department

Sorry I missed out information, I want the average Service Status for each department. So:

 

Title               Average ServiceStatus
IT Security     Stable
IT Security     Stable
IT Security     Stable
IT Security     Low Risk
IT Security     Low Risk
Mgt Info       Stable
Mgt Info       Low Risk

 

A summary in essence.

 

I see your formula has Table1 in it. Is this the name of the tables you create in the view? I can't seem to implement the formula properly.

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors