Based on the Status change, need ORD ID Count.

[Anonymous]

Hi All,

Below requirment might be easy but i am not able to get an idea how to achive. Thanks in Advance.

Requirement is:-

Scenario1: 1350 and 1361 is have Action Requested and Processed then need the Distinct Count of Ord ID.

Single Status, measn 1341 & 1351 is have only Processed need the count of ORD ID.

1342 &1370 is have only Action Requested this count of ORD ID is not needed.

ORD ID	Status
100001341	Processed
100001342	Action Requested
100001350	Action Requested
100001350	Processed
100001351	Processed
100001361	Action Requested
100001361	Processed
100001370	Action Requested

I need the count of Ord Ids based on the Status change. i need  calculation like Count of Actioned Requested/Count of Processed

1350+1361/1350+1361+1341+1351

 Two Actioned Requested and four Processesd.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can create a measure like this:

Change =
VAR tab =
    ADDCOLUMNS (
        'Table',
        "Count", CALCULATE ( COUNT ( 'Table'[ORD ID] ), ALLEXCEPT ( 'Table', 'Table'[ORD ID] ) )
    )
VAR a =
    COUNTX (
        FILTER ( tab, [Count] >= 2 && [Status] = "Action Requested" ),
        [ORD ID]
    )
VAR b =
    CALCULATE ( COUNT ( 'Table'[ORD ID] ), 'Table'[Status] = "Processed" )
RETURN
    DIVIDE ( a, b )

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

[Jos_Woolley]

Hi,

Actioned/Requested =
VAR MyTable =
    SUMMARIZE (
        'Table',
        'Table'[ORD ID],
        "Actioned", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Status] = "Action Requested" ),
        "Processed", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Status] = "Processed" )
    )
RETURN
    DIVIDE (
        SUMX ( MyTable, IF ( AND ( [Actioned] > 0, [Processed] > 0 ), 1 ) ),
        SUMX ( MyTable, IF ( [Processed] > 0, 1 ) )
    )

Regards