Forum Discussion

mumair's avatar
mumair
Helper I
9 years ago

Help with not double counting

My data looks like the following:

 

Shipment | Voyage | Product | Failed Tanks | Count of Failed Tanks

1000 | NA100 | A | 3D, 3E | 2

1001 | NA100 | A | 3D, 3E | 2

1002 | NA100 | A | 3D, 3E | 2

2000 | NA200 | A | 1S, 1P | 2

2001 | NA200 | B | 2S | 1

3000 | NA300 | A | N/A | 0

 

I want the number of failed tanks by voyage, so ideally the output would look something like the following:

 

Voyage | Count of Failed Tanks by Voyage

NA100 | 2

NA200 | 3

NA300 | 0

 

Could someone show me a way to do this please?

Need Help

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    mumair

    You'll need to re-model the dataset.

     

     

    Then create a measure like

    Count of Failed Tanks by Voyage = 
VAR cnt =
    CALCULATE (
        DISTINCTCOUNT ( yourTable[Failed Tanks] ),
        FILTER ( yourTable, yourTable[Failed Tanks] <> "N/A" )
    )
RETURN
    IF ( ISBLANK ( cnt ), 0, cnt )