Convert decimal aggregation into days and hours in Power BI

Question

I have a numerical column in Power BI and I display the average of it in a KPI card. It updates every day with data refresh. Is there any way to display the aggregated decimal number in days and hours like 3.5 should be 3 days and 12 hrs.

I also have a calculated measure which also gives me the same result in decimal, and I want that also to be displayed in days, and hours.

How accurate do you want it to be? Only minutes or also seconds if applicable? — ZygD, Jan 12 '21 at 08:15
Hi. Yes. In the provided answer you should add `FORMAT(_mean,"nn")` . Refer to the [documentation](https://learn.microsoft.com/en-us/dax/format-function-dax#custom-datetime-formats) for more info on how to use `FORMAT()`. — ZygD, Jan 16 '21 at 07:43

ZygD · Accepted Answer · 2021-01-12T09:07:38.683

This is one way how you could approach it.

Sample table "tbl1":

AVERAGE(tbl1[col1]) evaluates to 3.33...
AVERAGE(tbl1[col2]) evaluates to 3.5

Measure for col1:

avg_col1 = 
VAR _mean = AVERAGE(tbl1[col1])
RETURN INT(_mean) & " days " & FORMAT(_mean,"hh") & " hours"

Measure for col2:

avg_col2 = 
VAR _mean = AVERAGE(tbl1[col2])
RETURN INT(_mean) & " days " & FORMAT(_mean,"hh") & " hours"

Resulting cards:

Convert decimal aggregation into days and hours in Power BI

1 Answers1