Forum Discussion

surajde's avatar
surajde
Helper I
3 years ago

Calculating maxifs

Hi Team,    Please help calculating below MS Excel data using maxif funciton. Below is dummy data. I need to calculate result in Deferred Hours in PowerBI dashboard. Below is excel formula that wor...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi surajde ,

Please have a try.

COLUMN =
VAR _A2 =
    CALCULATE ( MAX ( Table1[TICKET_NUM] ), FILTER ( Table1, Table1[index] = 2 ) )
VAR _MAXIFS =
    CALCULATE (
        MAX ( Table1[ACTIVITY_TIME] ),
        FILTER (
            Table1,
            Table1[TICKET_NUM] = _A2
                && Table1[ACTIVITY_TYPE] = "Work Queue Update"
        )
    )
VAR _1LEFT =
    LEFT ( Table1[TICKET_STATE1], 8 )
VAR _g2 =
    CALCULATE ( MAX ( Table1[STOP_TIME] ), FILTER ( Table1, Table1[index] = 2 ) )
VAR _B2 =
    CALCULATE (
        MAX ( Table1[TICKET_OPENED] ),
        FILTER ( Table1, Table1[index] = 2 )
    )
VAR _F2 =
    CALCULATE ( MAX ( Table1[START_TIME] ), FILTER ( Table1, Table1[index] = 2 ) )
VAR _BIG =
    IF ( _B2 > _MAXIFS, _B2, _MAXIFS )
VAR _BIG2 =
    IF ( _F2 > _MAXIFS, _F2, _MAXIFS )
RETURN
    IF (
        _F2 <> BLANK ()
            && _g2 <> BLANK ()
            && _1LEFT = "Deferred"
            && _g2 >= _BIG,
        ( _g2 - _BIG2 ) * 24,
        0
    )

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Rongtie

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