Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Can you provide some sample data?
Yes:
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] )
Best Regards!
Dale
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
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
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
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.
Best Regards!
Dale
Thanks Dale - how would you create the second table? Using the formula you gave? Edit - just realised, drag the column again.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |