Count Shipments with Multiple Sources

[ajaffeeattmz]

I am trying to use DAX to determine from a table with tracking messages which shipments have multiple sources and what percentage of the total shipments have multiple sources. 

There is not a row in the table that has both sources, each row has a unique source. 

My test data is below and the result I am expecting is 33% (Shipment #1 is the only shipment that fits the criteria over the 3 shipments). 

Shipment #	Data Source
1	EDI
1	Track and Trace
2	EDI
3	Track and Trace

[Greg_Deckler · Accepted solution]

@ajaffeeattmz Try this:

Measure = 
  VAR __Table = SUMMARIZE( 'Table', [Shipment], "__Count", COUNTROWS( 'Table' ) )
  VAR __TotalCount = COUNTROWS( __Table )
  VAR __CountMultiple = COUNTROWS( FILTER( __Table, [__Count] > 1 ) )
  VAR __Result = DIVIDE( __CountMultiple, __TotalCount )
RETURN
  __Result

[ajaffeeattmz]

@Greg_Deckler 

I think I am almost there. In the example below, two of the 9 shipments meet the conditions, so the % should be 22%. In the below it shows that all of the shipments meet the condition. Where am I going wrong? 

[ajaffeeattmz]

@Greg_Deckler 

I got it to work. I just needed to add a calculate and filter to my summarize table variable.

[Greg_Deckler]

@ajaffeeattmz Can you share more/better representative data?