Sum numbers and distinct value

[m_lavova]

Dears,

ist there someone who would help me with me problem, please?

I have data in 3 columns: ASIN, STAR and UNITS SHIPED.

I need to select unique values from ASIN and sum UNITS SHIPED, but only for STAR = 1, 2, 3.

it looks like this:

ASIN    STAR   UNITS SHIPED
12             1                     2

12            1                     2

13           4                     1

14           2                     3

14           3                    3

the sum that I need is 5.

I tried a lot of possibilities, but it is not work.

Thank you in advance.

[agigliotti]

Hi,
You could try with:
Sum( {< STAR = {'1','2','3'} >} TOTAL <ASIN> DISTINCT UNITS SHIPED )
I hope it could helps.

[m_lavova]

 Thank you, but it does not work correctly

when I use your condition, I see not good values

[jonathandienst]

No need for total, just

Sum({<STAR = {'1','2','3'}>} [UNITS SHIPED])

[agigliotti]

with data provided above it does works.
what are dims and measures expressions of your bar chart ?

[m_lavova]

dimension is only business segment comes from source data (no expression) and as measure I used your expression

[agigliotti]

could you add "business segment" field to your data above to understand better what are you trying to achieve ?

[m_lavova]

ASIN    STAR   BUSINESS SEGMENT     UNITS SHIPED
12             1                          LED                                 2

12            1                          LED                                2

13           4                          TRAD                             1

14           2                           LUM                            3

14           3                           LUM                           3

I need to have similar result as you can see below, but for ASIN only unique values need to be genarated

[agigliotti · Accepted solution]

what about the below expression:
Sum( {< STAR = {'1','2','3'} >} DISTINCT [UNITS SHIPED] )
?

[m_lavova]

Thank you very  much,

it works correctly 🙂