find sum of total from calculated column

[Anonymous]

Hi

How to find the sum of total from the calculated columns ?

Can you please assist ?

Thanks

kunuthuri

[v-piga-msft]

Hi @Anonymous,

1. Create the calculated colum to calculate the difference of time.

hours = 'Table'[End_date]-'Table'[Start_date]

If you want to get the fomat, you could try the formula below.

format =
VAR allseconds =
    DATEDIFF ( [Start_date], [End_date], SECOND )
VAR hours =
    MOD ( INT ( allseconds / 60 / 60 ), 24 )
VAR minutes =
    MOD ( INT ( allseconds / 60 ), 60 )
VAR seconds =
    MOD ( allseconds, 60 )
RETURN
    IF (
        AND ( hours >= 1, minutes > 0 ),
        hours & "hr "
            & minutes
            & "mins ",
        IF (
            AND ( hours > 1, minutes = 0 ),
            hours & "hrs",
            IF ( AND ( hours = 0, minutes > 0 ), minutes & "mins" )
        )
    )

2. Create the measure to calculate total hours

Measure = CALCULATE(SUM('Table'[hours]))

If you also need the format, you could refer to the formula with if function above.

Here is the output.

You also could have a reference of the attachment.

Best Regards,

Cherry

[Anonymous]

Hi Cherry,

thanks to reply,

may i know query "format" is which data type?

because when i executed the query and measure is working fine, and once i drag it in to any widget, it is showing below error.

thanks

kunuthuri 

[Anonymous]

Hi

When i drag the measure

meas field = calculate(sum(table[difference hours]))

in to "Card" visual , it is showing below error.

[v-piga-msft]

Hi @Anonymous,

If you use farmat, the value should be a text type.

If you want to show the hours with format, you could calculate the totals hours with sum, then use if or switch function to format the values. 

Best Regards,

Cherry

[Anonymous]

Hi

can you please provide example to if or switch function format values?

Thanks

Kunuthuri

[v-piga-msft · Accepted solution]

Hi @Anonymous,

I have made a sample to format the total hours with the formula below.

foramt total hour =
VAR h =
    HOUR ( 'Table'[total hour] )
VAR m =
    MINUTE ( 'Table'[total hour] )
VAR s =
    SECOND ( 'Table'[total hour] )
RETURN
    SWITCH (
    TRUE (),
    h=0&&m=0&&s=0,"0",
    h=0&&m>0&&s>0,m&"m"&s&"s",
    h=0&&m>0&&s=0,m&"m",
    h=0&&m=0&&s>0,s&"s",
    h>=1&&m>=1&&s>=1,h&"hr"&m&"m"&s&"s",
    h>=1&&m=0&&s=0,h&"hrs",
    h>=1&&m>0&&s>0,h&"hr"&s&"s",
    h>=1&&m>=1&&s=0,h&"hr"&m&"m"    
)

Here is the result.

For the measure of total hour, you could refer to my first reply.

More details, you could have a view of my test pbix file attached below.

Best regards,

Cherry