Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with Count based on another column

I have an example data set:   ID Type Quantity 1 IP 1 2 IP 1 2 OP 7 3 IP 1   I want to sum the  Quantity of OP types where the unique IDs have an IP and OP Line item. ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    How about this?

     

    OP with IP =
VAR CalcTable =
    ADDCOLUMNS (
        VALUES ( Table01[ID] ),
        "@HasIP", "IP" IN CALCULATETABLE ( VALUES ( Table01[Type] ) ),
        "@QtyOP", CALCULATE ( SUM ( Table01[Quantity] ), Table01[Type] = "OP" )
    )
RETURN
    SUMX ( FILTER ( CalcTable, [@HasIP] ), [@QtyOP] )
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

How about this?

 

OP with IP =
VAR CalcTable =
    ADDCOLUMNS (
        VALUES ( Table01[ID] ),
        "@HasIP", "IP" IN CALCULATETABLE ( VALUES ( Table01[Type] ) ),
        "@QtyOP", CALCULATE ( SUM ( Table01[Quantity] ), Table01[Type] = "OP" )
    )
RETURN
    SUMX ( FILTER ( CalcTable, [@HasIP] ), [@QtyOP] )