DAX code

[Anonymous]

Hey guys, 
I'll try to describe my issue as detailed as possible. So in my report i am aggregating time using Chelsie Eiden's Duration model, where it lets me have whole number in time format and that way use it in the values field in the histogram. This part works great, however there is a metric of 04:30:00 hour, which should not be exceeded by the initial time. If it is exceeded, then it should be displayed in red. What I want to do is that in the histogram it would take the actual time and if it is under 4:30 or 4:30, simply show it as actual time in green, but let's say the whole time is 7:30, i would like it to show 4:30 in green and then the rest(overtime) in red in a stacked column chart. Any ideas how to get around this problem? 

First column on the left is the actual time in whole number format and first column from the right is in the same format and it's metric that should not be exceeded

This is the following code I use to get my time: 

// Duration formatting 
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = SUM([Duration])
// There are 3,600 seconds in an hour
VAR Hours = INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
RETURN
// We put the hours, minutes and seconds into the proper "place"
Hours * 10000 + Minutes * 100 + Seconds

So now I've got the histogram looking like that, but that first column, everything over 4:30 should be in red and should display that overtime on it

[amitchandak]

@Anonymous , Not sure I got.

But you can a measure like and use that in conditional formatting with the "Field value" option

Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[Anonymous]

@amitchandak  This is more with the time and I don't think I can use average value as I need to compare exact valuse against each other, For instance : If time is 7:30 then 4:30 should be shown in green with value shown as 4:30 and then 3 hours shown in red. I believe i would need to create a custom column?

[Pragati11]

HI @Anonymous ,

This doesn't help much. Please add more details to your query:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Thanks,

Pragati

[Anonymous]

@Pragati11 Updated