Sum of a overall category

[Manuka]

Hi together,

I need your support, hoping anyone has an idea how to solve my problem. 

In the top table I have my raw data example in Column A:C. 

In the first step, I set the overall status in Column D (one Task is open = overall status is open, etc...). If not necessary to have a new calculated column, I would prefer to have this step integrated in a measure. 

In the second step, I need to count the amount of serial Numbers with the overall status "open", "done", "not affected". 

My thought is to have three DAX Measures, for each overall status one which have the result shown in the blue box. 

Regards, Manuka

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Open count: = 
VAR _t =
    ADDCOLUMNS (
        VALUES ( Data[Serial Number] ),
        "@overallstatus",
            SWITCH (
                TRUE (),
                COUNTROWS (
                    FILTER (
                        CALCULATETABLE ( VALUES ( Data[Status per Task] ) ),
                        Data[Status per Task] <> "Not affected"
                            && Data[Status per Task] = "Open"
                    )
                ) > 0, "Open",
                COUNTROWS (
                    FILTER (
                        CALCULATETABLE ( VALUES ( Data[Status per Task] ) ),
                        Data[Status per Task] <> "Not affected"
                            && Data[Status per Task] = "Done"
                    )
                ) > 0, "Done",
                "Not Affected"
            )
    )
RETURN
    COUNTROWS ( FILTER ( _t, [@overallstatus] = "Open" ) )

Done count: = 
VAR _t =
    ADDCOLUMNS (
        VALUES ( Data[Serial Number] ),
        "@overallstatus",
            SWITCH (
                TRUE (),
                COUNTROWS (
                    FILTER (
                        CALCULATETABLE ( VALUES ( Data[Status per Task] ) ),
                        Data[Status per Task] <> "Not affected"
                            && Data[Status per Task] = "Open"
                    )
                ) > 0, "Open",
                COUNTROWS (
                    FILTER (
                        CALCULATETABLE ( VALUES ( Data[Status per Task] ) ),
                        Data[Status per Task] <> "Not affected"
                            && Data[Status per Task] = "Done"
                    )
                ) > 0, "Done",
                "Not Affected"
            )
    )
RETURN
    COUNTROWS ( FILTER ( _t, [@overallstatus] = "Done" ) )

Not affected count: = 
VAR _t =
    ADDCOLUMNS (
        VALUES ( Data[Serial Number] ),
        "@overallstatus",
            SWITCH (
                TRUE (),
                COUNTROWS (
                    FILTER (
                        CALCULATETABLE ( VALUES ( Data[Status per Task] ) ),
                        Data[Status per Task] <> "Not affected"
                            && Data[Status per Task] = "Open"
                    )
                ) > 0, "Open",
                COUNTROWS (
                    FILTER (
                        CALCULATETABLE ( VALUES ( Data[Status per Task] ) ),
                        Data[Status per Task] <> "Not affected"
                            && Data[Status per Task] = "Done"
                    )
                ) > 0, "Done",
                "Not affected"
            )
    )
RETURN
    COUNTROWS ( FILTER ( _t, [@overallstatus] = "Not affected" ) )

[Manuka]

Thats perfect, as I need them each in a separate measure that is working fine! Thanks a lot for your time and feedback!

[Anonymous]

Hi @Manuka 

Please try this:

I create 2 measures with the data you provided:

Status =
VAR _currentNumber =
    MAX ( 'Table'[Serial Number] )
VAR _vtable =
    SELECTCOLUMNS (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Serial Number] = _currentNumber ),
        'Table'[Status per Task]
    )
RETURN
    IF (
        "Open" IN _vtable,
        "Open",
        IF (
            "Done" IN _vtable,
            "Done",
            IF ( "Not affected" IN _vtable, "Not affected" )
        )
    )

Count =
VAR _vtable =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[Serial Number],
        "_Status", [Status]
    )
RETURN
    COUNTROWS (
        FILTER ( _vtable, [_Status] = SELECTEDVALUE ( 'Table'[Status per Task] ) )
    )

The result is as follow:

Best Regards

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