cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lalTel20
Helper I
Helper I

Show value as days and hours and aggregate

So i'm trying to display the below value in the format "x days, y hours" but when i change the format, it does not aggregate being a text output. Has anyone worked on this? Can we have the value in the background but still display as human readable format of days and hours. 
 
lalTel20_0-1637134656813.png

 

5 REPLIES 5
amitchandak
Super User
Super User

@lalTel20 , We can not use text format measure on bar visual

 

try of you can give format here - property under data model

 

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

Thankyou @amitchandak for your suggestions.  I have now tried this out. Only challenge now is the fractional day doesnt get converted to hours, but only shows as fractional hours. 
so 0.83 days i am expecting it to convert to 20 hours. 

 

The other alternative I tried was splitting hours in days and hours 

var days = INT(total_hours/24)
var hours = MOD(total_hours,24)/100 and adding them up to show the correct days and hours. However as expected they wouldnt add up to the totals. 

lalTel20_0-1637196143067.png

 

Hi @lalTel20 ,

 

What's your data model? Can you provide me with some dummy data and expected results?

 

Best Regards,

Stephen Tao

 

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

Hi @v-stephen-msft ,

sample Data - 

StoreAttributeStart DurationDCM CaseEnd DurationCalendar Duration ElapsedBusiness Duration Elapsed
BallaratDate Raised4/11/2021 16:51caseid01889645/11/2021 17:321.0284722221.028472222
BallaratStore Pickup5/11/2021 17:32caseid01889648/11/2021 9:362.6694444440.669444444
BallaratDelivered To Warehouse8/11/2021 9:36caseid018896410/11/2021 16:232.2826388892.282638889
BallaratPickup from Warehouse10/11/2021 16:23caseid018896411/11/2021 14:080.906250.90625
BallaratDelivered to Store11/11/2021 14:08caseid018896411/11/2021 14:380.0208333330.020833333
BendigoDate Raised8/11/2021 10:48caseid01914099/11/2021 15:511.2104166671.210416667
BendigoStore Pickup9/11/2021 15:51caseid019140910/11/2021 14:150.9333333330.933333333
BendigoDelivered To Warehouse10/11/2021 14:15caseid019140911/11/2021 16:331.0958333331.095833333
BendigoPickup from Warehouse11/11/2021 16:33caseid019140912/11/2021 10:370.7527777780.752777778
BendigoDelivered to Store12/11/2021 10:37caseid019140912/11/2021 11:070.0208333330.020833333

 

Current outcome - 

lalTel20_0-1637799806331.png

need to update the days value from decimal format such as 6.375 days --> 6 days 9 Hrs 

 

Hi @lalTel20 ,

 

Sorry for my late reply.

Below is how I show you how to display the change from 6.375 days to 6 days 9 Hrs.

First, create a calculated column as follows.

DATEDIFF is to get the hours of the time difference. Divide the number of hours calculated by 24 to get the number of days, and then multiply by 100 plus the remainder of the number of hours divided by 24.

For example, _hours=153 → 609

DayHours = var _hour=DATEDIFF([Start Duration],[End Duration],HOUR)
return INT(DIVIDE(_hour,24))*100+MOD(_hour,24)/100

And then create a custom format like

vstephenmsft_0-1638932292848.png

Now, "609" is changed to "6Days09Hrs".

vstephenmsft_1-1638932300950.png

"None" should be seleceted in Display units.

vstephenmsft_2-1638932315657.png

 

 

Best Regards,

Stephen Tao

 

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

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors