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 d...
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
     

     

     

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
     

     

     

    • PaulMcDk's avatar
      PaulMcDk
      Frequent Visitor
      4 years ago

      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 
      ShipmentMaterialBRAND
      123456771….1TUNA 
      123456771….2TUNA 
      123456771….3SALMON
      123456771….4SALMON
         
      234567771….1TUNA 
      234567771….2TUNA 
      234567771….3SALMON
      234567771….4SALMON
         
      345678771….1TUNA 
      345678771….2TUNA 
      345678771….3SALMON
      345678771….4SALMON
         
      Table B - Pallet  
      ShipmentMaterialPallet
      123456771….13
      123456771….25
      123456771….318
      123456771….47
         
      234567771….15
      234567771….29
      234567771….311
      234567771….414
         
      345678771….132
      345678771….21
      345678771….32
      345678771….45

       

      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 !