duration chart showing values in hours and minutes (avoid "count of") Help

[Robert_715]

Hi all, I have the list of Operations, each of them has its “Start date” and “End date” (both are Date/time data type), (as example Start date is 2022-09-01 02:15:00 ), in separate columns

In an added separate column, I calculate The Duration = [End date] – [Start date] and the result is also the Date/Time type of data, say 07:15:00

I create the Clustered column chart where I place Operations on Axis, ant the duration on Values expecting to have the series of columns of different height representing different durations of each operations, with its values shown on each column in format 07:15

However, all the columns of each operation are generated equal, with value “1”. I found that power BI counts them instead of simply showing their value/figures on chart columns. It shows “1” since each operation has the only value, so naturally the result of counting is 1

But how could I force power BI to show the real values of duration, say “07:30” instead of “1” on the columns of the chart.

I tried to do it using

Duration = (DATEDIFF([Start date],[End date],MINUTE))/60

But the result, of course, is not 07:30, but 7,5 (which means simply seven hours and a half, which makes chart not so informative)  

Thank you for your advice!   

[v-yanjiang-msft]

Hi @Robert_715 ,

The Y-axis of column chart or X-axis of bar chart are all about showing the size of the value. If it's a number type, there're many ways to present values(sum, average, count etc.)

But if the column is of a data type other than number, there're only two options to display the value, Count or Count(Distinct), it's by design.

Best Regards,
Community Support Team _ kalyj

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

[Adel]

For this, there is a quite complicated solution, that was explained to us by a data integration tool  windsor.ai .

First you would need to create a time difference column using this formula.

Duration = (DATEDIFF([Start date],[End date],SECOND))

Then 

Duration (Days, Hours, Mins & Secs) - Measure = 
 
        // This retrieves the total duration in Seconds
        VAR _Seconds = [Duration (Seconds)]

        // This calculates the total working duration in Minutes
        VAR _Minutes = INT(_Seconds/60)
 
        // This computes the total minutes remaining 
        VAR _MinsLeft = MOD(_Minutes, 60)
 
        // This computes the total seconds remaining 
        VAR _SecsLeft = MOD(_Seconds,60)
 
        // This computes the total working duration in Hours
        VAR _Hours = INT(_Minutes/60)
 
        // This computes the total hours remaining 
        VAR _HrsLeft = MOD(_Hours, 24)
 
        // This computes the total working duration in Days
        VAR _Days = INT(_Hours/24)
 
        // This computes a string combination of the relevant dates to output the duration in Days, Hours, Minutes, and Seconds
        VAR _Result = _Days&" Days "
            &_HrsLeft&" "&"hrs "
            &_MinsLeft&" "&"Mins "
            &_SecsLeft&" "&"Secs"
    
    RETURN
        _Result

[Robert_715]

Hello, Adel,

Thank you for your prompt response and hints.

Could you clarify a couple more things:

1) in a first step (

we create column (calculating time difference in seconds),

but in second step - we create measure (

 Duration (Days, Hours, Mins & Secs) - Measure) not column ?

2) I created time difference column (as in 1th step) named Duration and then created measure with the formula you wrote. 

In the 4th row of the formula Power BI writes comment it cannot find name '[Duration (Seconds)]' 

But '[Duration (Seconds)]' is a command that generates variable VAR_Seconds, isn't it ? why this line

        VAR _Seconds = [Duration (Seconds)]

confuses Power BI ?

Thank you