cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mumair Regular Visitor
Regular Visitor

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?

2 REPLIES 2

Re: Help with not double counting

=calculate(countrows(tablename),tablename[failed tanks]<>"N/A")



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Moderator Eric_Zhang
Moderator

Re: Help with not double counting

@mumair

You'll need to re-model the dataset.

 

Capture.PNG

Capture2.PNG

 

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 )

Capture.PNG

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors