Issues with DurationFormatted in Power BI Visuals

[manoj_0911]

Hi everyone,

I'm encountering some challenges while working with a calculated column named `ACW DurationFormatted` (converts seconds to HH:MM:SS) in Power BI visuals.

**Card Visual Issue:**

1. **Problem:** When I drag `ACW DurationFormatted` to a card visual, it only displays "00:00:00."
2. **Current Selection:** The fields selection in the card visual is set to "First" by default.

**Question:**

* What am I missing here? How can I display the sum or average of the `ACW DurationFormatted` values in the card visual?
* How should the calculation change for sum or average aggregation within the card visual?

**Line Chart Issue:**

1. **Problem:** When I drag `ACW DurationFormatted` to the X-axis of a line chart and `Weekday` to the Y-axis, the visualization changes `Weekday` to "Count of Weekday."
2. **Desired Outcome:** I want to see the trend of average or sum of `ACW DurationFormatted` by weekday.

**Question:**

* How can I fix this and display the desired trend with weekday on the X-axis and average/sum of `ACW DurationFormatted` on the Y-axis?

**Additional Information:**

* `ACW DurationFormatted` is a calculated column using formulas to convert seconds to HH:MM:SS format.

ACW Hours = INT([ACW_DURATION] / 3600)
ACW Minutes = INT(MOD([ACW_DURATION], 3600) / 60)
ACW Seconds = MOD([ACW_DURATION], 60)

ACW DurationFormatted =
FORMAT([ACW Hours], "00") & ":" &
FORMAT([ACW Minutes], "00") & ":" &
FORMAT([ACW Seconds], "00")

------------------------------------------

Weekday = FORMAT([IXN_SUBHOUR_DATE],"DDD")

Thanks in advance for your assistance!

Manoj Prabhakar

[Greg_Deckler · Accepted solution]

@manoj_0911 This is because you are ending up with a duration that is text. You need to use custom format strings like this: Chelsie Eiden's Duration - Microsoft Fabric Community

[v-jianpeng-msft · Accepted solution]

Thanks @Greg_Deckler 

Hi, @manoj_0911 

Have you solved the current problem now? I tested your DAX expression and your question using the sample data below:

First I created the following calculated columns using the DAX you provided, and since creating the calculated columns here would have a circular dependency, I added ALLEXCEPT('Table (2)', 'Table (2)'[seconds]) to each DAX:

In my tests, the card visual was indeed able to show the first value, indicating that there is no problem with your DAX, as shown in the following image:

For the following two problems, I use the following DAX expression to sum and average:

How can I display the sum or average of the `ACW DurationFormatted` values in the card visual?
How should the calculation change for sum or average aggregation within the card visual?

Average ACW DurationFormatted1 =
VAR _totalsecond =
    CALCULATE ( SUM ( 'Table (2)'[seconds] ), ALL ( 'Table (2)' ) )
VAR _hours =
    INT ( DIVIDE ( _totalsecond / 3600, DISTINCTCOUNT ( 'Table (2)'[seconds] ) ) )
VAR _Minutes =
    INT (
        DIVIDE (
            MOD ( _totalsecond, 3600 ) / 60,
            DISTINCTCOUNT ( 'Table (2)'[seconds] )
        )
    )
VAR _second =
    INT (
        DIVIDE ( MOD ( _totalsecond, 60 ), DISTINCTCOUNT ( 'Table (2)'[seconds] ) )
    )
RETURN
    _hours & ":" & _Minutes & ":" & _second

Sum ACW DurationFormatted =
VAR _totalsecond =
    CALCULATE ( SUM ( 'Table (2)'[seconds] ), ALL ( 'Table (2)' ) )
VAR _hours =
    INT ( _totalsecond / 3600 )
VAR _Minutes =
    INT ( MOD ( _totalsecond, 3600 ) / 60 )
VAR _second =
    MOD ( _totalsecond, 60 )
RETURN
    _hours & ":" & _Minutes & ":" & _second

Both DAX expressions are used to create measures. The idea of calculating the average value is to add all the seconds and then average the Hour, minutes, and seconds respectively. The idea of summing is to first find the total number of seconds, and then calculate the hours, minutes, and seconds corresponding to this total number of seconds. 

Here are the results:

How can I fix this and display the desired trend with weekday on the X-axis and average/sum of `ACW DurationFormatted` on the Y-axis?

For the above problem, since ACW DurationFormatted is a Text type data, it cannot be put into the Y axis.

If you place this one calculated column in the Y-axis, it will only count that column, not show the composite or average.

The main reason for this is that ACW DurationFormatted is not a value, but a text. I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Greg_Deckler · Accepted solution]

@manoj_0911 This is because you are ending up with a duration that is text. You need to use custom format strings like this: Chelsie Eiden's Duration - Microsoft Fabric Community