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
AW1976NOVA
Post Patron
Post Patron

Calculate Average Speed to Answer

Hi,

 

I have a table 'QMC Dashboard' I'm brining into Power BI that has four columns.  One column is 'Category'.  Second column is 'Date'.  Third column is 'Numerator'.  And fourth column is 'Denominator'.

 

The Category column can populate with 8 different results.  The Date column is a date.  The Numerator column is a whole number.  The Denominator column is a whole number.

 

What I'm attempting to do is to plot the 'Average Speed to Answer' on a Chart type visual.  Essentially, I'd like to re-create the below Chart in Power BI:

 

aaaa.PNG

 

The problem I'm running into is that the 'Numerator' column is a whole number.  I would like to transform these values in to the following format.  For demonstration purposes here are a few examples.

Eg1) If Numerator value populates as 30 (whole number), I'd like this to transform in to ":30" ... representing 30 mins.

Eg2) If Numerator value populates as 121 (whole number), I'd like this to transform to "2:01" ... representing 2 hours and 1 minute.

Eg3) If Numerator values populates as 2 (whole number), I'd like this to transform to ":02" ... representing 2 minutes.

 

To complicate things, the 'Numerator column doesn't only represent minutes.  If the Category column populates as 'ASA', then yes; Numerator column (whole number values) represent minutes.  However, if Category column populates as 'Complaints', or 'Sales', or 'Audit' ... the whole number value represents something other than minutes.

 

I've tried duplicating the Numerator column in 'Edit Queries'.  I then attempted to transform it to minutes but was unable to make this happen.  I don't think that would work anyway because I don't think a Power BI visual table understands how to plot values such as :30, :1:00, 1:30 or :02 on a Y-Axis anyway.

 

Has anyone attempted to re-create something like this?  Is this possible with DAX?

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @AW1976NOVA,

I'd like to suggest you write a measure formula to aggregate on your numeric fields and enabled string formatting on your table fields. For string formatting feature, you can refer to the following document to know more about this:

Use custom format strings in Power BI Desktop 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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.