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
Icon for Most Valuable Professional rankMost 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 !