Multiply Minutes times Quantity

[pawelj795]

Hi,
I have two measures like below.



Now, I want to multiply them, but when I do that, the results are decimal numbers.
How to change them to Minutes/Hours?

[v-gizhi-msft]

Hi,

Please try this measure:

Measure = FORMAT(SUM(TSENB_CUSTINVOICETRANS[QTY])*SUM('Production Standards'[Time]),"HH:MM:SS")

Hope this helps.

Best Regards,

Giotto

[pawelj795]

@v-gizhi-msft 
Your solution works, thanks.

But, I can't figure out issue with suming time (Total).

Why isn't correct?

[amitchandak]

@pawelj795 , for correct GT you have force row context, try like

Measure = FORMAT(SUMX(Summarize('Production Standards', 'Production Standards'[Item],"_1",Sum(TSENB_CUSTINVOICETRANS[QTY]),"_2",SUM('Production Standards'[Time])),[_1]*[_2]),"HH:MM:SS")

[pawelj795]

Same issue even with columns.

[v-gizhi-msft]

Hi,

It is because the measure total depends on context not the current field in visual.

This is a classical measure total issue.

Please try this:

Measure =
FORMAT (
    SUMX (
        DISTINCT ( TSENB_CUSTINVOICETRANS[Item name] ),
        TSENB_CUSTINVOICETRANS[QTY] * 'Production Standards'[Time]
    ),
    "HH:MM:SS"
)

Hope this helps.

Best Regards,

Giotto

[pawelj795]

@v-gizhi-msft 
@amitchandak 

I tried both of your's solution but it still didn't work.

In some cases the results are correct but in most cases are incorrect.

[amitchandak]

@pawelj795 , New column in minute

New column = hour([Time])*60 + minute([Time]) // Change data type from whole number to decimal

[pawelj795]

@amitchandak 

The problem is that I have indirect relationship between these two tables.
That's why I must use measures instead of columns.

[amitchandak]

You can use

sumx(Table,hour(Table[Time])*60 + minute(Table[Time]))

But mutiplcation need to done on a common row context . Refer

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

You may have use summarize or values

[pawelj795]

@amitchandak 
Okay I get it, thanks.

But is there any way to display hours/minutes/seconds in measure as time format like below column?

[amitchandak]

@pawelj795 , refer https://community.powerbi.com/t5/Desktop/Sum-of-H-MM-SS/td-p/124007