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
cuskav
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

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
Anonymous
Not applicable

Can you provide some sample data?

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!

 

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.

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

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.

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.

 

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.

Hi Dale,

 

I'm looking back on the solution you provided. I'm trying to get that last table you show which shows just the Title and Average Service Status. I'm not sure you provided the full solution. I've created the measure of

Average = COUNT( 'Service Health Checks'[ServiceStatus] ) / DISTINCTCOUNT ( 'Service Health Checks'[ServiceStatus])

but I think in our final table (the one I want!) I think you might use that measure within a calculation. I'm not sure you provided that. Any chance you could give me further help with this?

Hi @cuskav,

 

The measure evaluates the number of ServiceStatus of each ServiceStatus. Maybe it's meaningless. Could you please explain what you want exactly? I have no idea about  "measure within a calculation".

 

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.

Thanks - I'm one of these people who ask the question first, then go looking for the answer while I'm waiting for a response. I find it, then feel embarassed that I already know.

 

You've bene a great help.

 

I'll mark your answer as a solution.

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.

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

 

@cuskav Have you tried the Group By option?

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.