Forum Discussion

PaulMcDk's avatar
PaulMcDk
Frequent Visitor
5 years ago
Solved

SUM + DISTINCTCOUNT support

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]

352325426
352325333

 

This is the table

 

ShipmentMaterialCountry KeyPallet Number
352325369199001Bulgaria1
352325377200895Bulgaria1
352325377201310Bulgaria1
352325377200895Bulgaria3
352325377200895Bulgaria3
352325377199968Bulgaria2
352325377200895Bulgaria5
352325377199968Bulgaria11
352325377204562Bulgaria0
352325377200895Bulgaria0
352325377199968Bulgaria0
352325377204562Bulgaria7
352325469199001Bulgaria26
352325477205189Bulgaria2
352325477201385Bulgaria2
352325477206282Bulgaria1
352325477206282Bulgaria1
352325477198412Bulgaria4
352325477201237Bulgaria1
352325477202561Bulgaria2
352325477205189Bulgaria1
352325477205189Bulgaria1
352325477205189Bulgaria1
352325477134316Bulgaria1
352325477201385Bulgaria0
352325477175551Bulgaria0
352325477202561Bulgaria0
352325477206761Bulgaria0
352325477206282Bulgaria0
352325477181972Bulgaria0
352325477198412Bulgaria0
352325477205189Bulgaria0
352325477175551Bulgaria1
352325477175551Bulgaria2
352325477206507Bulgaria1
352325477181972Bulgaria2
352325477206552Bulgaria1
352325477206761Bulgaria2

 

Thank you again !

Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    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
     

     

     

6 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    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's avatar
      PaulMcDk
      Frequent Visitor
      5 years ago

      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.

       

      ShipmentMaterialCountry KeyPallet Number
      352325369199001Bulgaria1
      352325377200895Bulgaria1
      352325377201310Bulgaria1
      352325377200895Bulgaria3
      352325377200895Bulgaria3
      352325377199968Bulgaria2
      352325377200895Bulgaria5
      352325377199968Bulgaria11
      352325377204562Bulgaria0
      352325377200895Bulgaria0
      352325377199968Bulgaria0
      352325377204562Bulgaria7
          

       

      • selimovd's avatar
        selimovd
        Most Valuable Professional
        5 years ago

        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
         

         

         

  • hashtag_pete's avatar
    hashtag_pete
    Helper V
    5 years ago

    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