Forum Discussion
Solved
Count Shipments with Multiple Sources
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 |
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
4 Replies
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 __ResultI 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 Can you share more/better representative data?
