topic Re: Sum of a overall category in DAX Commands and Tips

Sum of a overall category

Manuka — Wed, 03 Jul 2024 14:28:52 GMT

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

Re: Sum of a overall category

Anonymous — Thu, 04 Jul 2024 02:58:16 GMT

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.

Re: Sum of a overall category

Jihwan_Kim — Thu, 04 Jul 2024 03:08:02 GMT

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" ) )

Re: Sum of a overall category

Manuka — Thu, 04 Jul 2024 12:31:30 GMT

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