Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pawelj795
Post Prodigy
Post Prodigy

Multiply Minutes times Quantity

Hi,
I have two measures like below.
image.pngimage.png


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

11 REPLIES 11
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try this measure:

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

Hope this helps.

 

Best Regards,

Giotto

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

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

Why isn't correct?
image.png

@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")

Same issue even with columns.
image.png

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

@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.

image.png

amitchandak
Super User
Super User

@pawelj795 , New column in minute

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

@amitchandak 

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

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

@amitchandak 
Okay I get it, thanks.

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.