SUMIFs in PowerBI?

Hi Anonymous ,

We add some fake data into yours to have expected data, if using orgin data, it will get result 20/20 = 100%.

then we can just create  a calculated table to meet your requirement:

Test = 
ADDCOLUMNS (
    ADDCOLUMNS (
        ADDCOLUMNS (
            GROUPBY (
                'Table',
                'Table'[Distribution Center],
                'Table'[Date].[Year],
                'Table'[Date].[MonthNo],
                'Table'[Plate]
            ),
            "Number of operating days", COUNTROWS (
                DISTINCT (
                    SELECTCOLUMNS (
                        FILTER (
                            'Table',
                            [Distribution Center] = EARLIER ( [Distribution Center] )
                                && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] )
                                && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] )
                        ),
                        "DateCount", [Date]
                    )
                )
            ),
            "#Days Vehicle Showed Up", COUNTROWS (
                DISTINCT (
                    SELECTCOLUMNS (
                        FILTER (
                            'Table',
                            [Distribution Center] = EARLIER ( [Distribution Center] )
                                && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] )
                                && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] )
                                && [Plate] = EARLIER ( 'Table'[Plate] )
                        ),
                        "DateCount", [Date]
                    )
                )
            )
        ),
        "% Of Appearances", DIVIDE ( [#Days Vehicle Showed Up], [Number of operating days], 0 )
    ),
    "Type", IF ( [% Of Appearances] >= 0.6, "reliable", "not reliable" )
)

but if you want the calculated column in origin table, we can use the following formula:

Number of operating days = 
COUNTROWS (
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                'Table',
                [Distribution Center] = EARLIER ( [Distribution Center] )
                    && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] )
                    && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] )
            ),
            "DateCount", [Date]
        )
    )
)

#Days Vehicle Showed Up =
COUNTROWS (
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                'Table',
                [Distribution Center] = EARLIER ( [Distribution Center] )
                    && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] )
                    && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] )
                    && [Plate] = EARLIER ( 'Table'[Plate] )
            ),
            "DateCount", [Date]
        )
    )
)

% Of Appearances =
DIVIDE ( [#Days Vehicle Showed Up], [Number of operating days], 0 )

Type = 
IF ( [% Of Appearances] >= 0.6, "reliable", "not reliable" )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.