SUM + DISTINCTCOUNT support

[PaulMcDk]

Dear community,

first, thanks for great support you are providing.

I have this table with #2 different Shipment (first column).

I would like to have a measure - called "pallet number" that does, for each Shipment, the column R (Cty) by filtering : NOT Material (691*) and Country Key ("Bulgaria").

So for example the expected result should :

Shipment	[Pallet Number]
3523254	26
3523253	33

This is the table

Shipment	Material	Country Key	Pallet Number
3523253	69199001	Bulgaria	1
3523253	77200895	Bulgaria	1
3523253	77201310	Bulgaria	1
3523253	77200895	Bulgaria	3
3523253	77200895	Bulgaria	3
3523253	77199968	Bulgaria	2
3523253	77200895	Bulgaria	5
3523253	77199968	Bulgaria	11
3523253	77204562	Bulgaria	0
3523253	77200895	Bulgaria	0
3523253	77199968	Bulgaria	0
3523253	77204562	Bulgaria	7
3523254	69199001	Bulgaria	26
3523254	77205189	Bulgaria	2
3523254	77201385	Bulgaria	2
3523254	77206282	Bulgaria	1
3523254	77206282	Bulgaria	1
3523254	77198412	Bulgaria	4
3523254	77201237	Bulgaria	1
3523254	77202561	Bulgaria	2
3523254	77205189	Bulgaria	1
3523254	77205189	Bulgaria	1
3523254	77205189	Bulgaria	1
3523254	77134316	Bulgaria	1
3523254	77201385	Bulgaria	0
3523254	77175551	Bulgaria	0
3523254	77202561	Bulgaria	0
3523254	77206761	Bulgaria	0
3523254	77206282	Bulgaria	0
3523254	77181972	Bulgaria	0
3523254	77198412	Bulgaria	0
3523254	77205189	Bulgaria	0
3523254	77175551	Bulgaria	1
3523254	77175551	Bulgaria	2
3523254	77206507	Bulgaria	1
3523254	77181972	Bulgaria	2
3523254	77206552	Bulgaria	1
3523254	77206761	Bulgaria	2

Thank you again !

[PaulMcDk]

that's great ! bravo

[hashtag_pete]

Hello Paul, 

try this:

Distinct Count = SUMX( CALCULATETABLE( Tabelle1, Tabelle1[Country Key] = "Bulgaria", LEFT(Tabelle1[Material],3) <> "691"), Tabelle1[Pallet Number] )

This worked for me in a table visualisation:

Cheers

hashtag_pete

[selimovd]

Hey @PaulMcDk ,

I understood the requirements, but how do you end up with the result 26? What should 26 be?

Can you explain the result a little better?

Thank you and best regards

Denis

[PaulMcDk]

Ciao @selimovd ,

33 is the sum of each line for the shipment 3523253 excluding the first line with material "69199001"

So basically the sum of column Pallet Number.

Shipment	Material	Country Key	Pallet Number
3523253	69199001	Bulgaria	1
3523253	77200895	Bulgaria	1
3523253	77201310	Bulgaria	1
3523253	77200895	Bulgaria	3
3523253	77200895	Bulgaria	3
3523253	77199968	Bulgaria	2
3523253	77200895	Bulgaria	5
3523253	77199968	Bulgaria	11
3523253	77204562	Bulgaria	0
3523253	77200895	Bulgaria	0
3523253	77199968	Bulgaria	0
3523253	77204562	Bulgaria	7

[selimovd · Accepted solution]

Hey @PaulMcDk ,

thank you for the explanation, now it makes sense 😊

The following measure should give you the result you want:

Pallet Number NEW =
CALCULATE(
    SUM( 'Table'[Pallet Number] ),
    LEFT( 'Table'[Material], 3 ) <> "691" && 'Table'[Country Key] = "Bulgaria"
)

And that's the result:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[PaulMcDk]

Dear Selimovd,

how are you doing ?

I'm using the solution you suggested but i notice it has a limit (not depending on you).

Formula is fine if i want to count #pallet per shipment BUT if i add another dimension it is not working .

For istance:

Table A - Shipment
Shipment	Material	BRAND
123456	771….1	TUNA
123456	771….2	TUNA
123456	771….3	SALMON
123456	771….4	SALMON
234567	771….1	TUNA
234567	771….2	TUNA
234567	771….3	SALMON
234567	771….4	SALMON
345678	771….1	TUNA
345678	771….2	TUNA
345678	771….3	SALMON
345678	771….4	SALMON
Table B - Pallet
Shipment	Material	Pallet
123456	771….1	3
123456	771….2	5
123456	771….3	18
123456	771….4	7
234567	771….1	5
234567	771….2	9
234567	771….3	11
234567	771….4	14
345678	771….1	32
345678	771….2	1
345678	771….3	2
345678	771….4	5

the two tables are linked with a relation Many:Many via "Shipment" key . 

Now, if i want to have a new table with:

BRAND       PALLET #

TUNA

SALMON

The formula is counting the total number of shipment without considering the BRAND.

So the total (or subtotal) per BRAND is wrong .

Any idea ?

thank you !