SUM VALUES FROM 2 DISTINCT COLUMNS

[eversoncarvalho]

Hello, people.

As you guys can see on the table above, I´have more than one "seller" per "order_num" and it’s ok. But I need to SUM the "sku_qtd" without repeating the items from the order. Right now, SUM is returning 6 instead of 3.

Pls help me, I have been looking for an answer for hours.

[tamerj1]

Hi @eversoncarvalho 
You can try

sku_qtd =
DIVIDE (
    SUM ( TableName[ColumnName(SKU Qty)] ),
    DIVIDE ( COUNTROWS ( TableName ), ( TableName[ColumnName(SKU Name)] ) )
)

[SpartaBI]

@eversoncarvalho 
If you just want to count the number of sellers do:
Measure = DISTINCTCOUNT('Table'[seller])
If you just want to count the number of sku's do:
Measure = DISTINCTCOUNT('Table'[squ_name])
If you want to count the number of sellers that will add app on different orders create this measure:
Measure = SUMX(VALUS('Table'[order_num]),DISTINCTCOUNT('Table'[seller]))
If you want to count the number of squ's that will add app on different orders create this measure:
Measure = SUMX(VALUS('Table'[order_num]),DISTINCTCOUNT('Table'[squ_name]))

'Table' is the name of your table so replace that in the code.

Showcase Report – Contoso By SpartaBI

     

[eversoncarvalho]

I want to know the amount sold by sku_qtd without repeating the sku_name and order_num

[SpartaBI · Accepted solution]

@eversoncarvalho 
Measure = SUMX(SUMMARIZE('Table', 'Table'[order_num],'Table'[squ_name]), ,CALCULATE(AVERAGE('Table'[squ_qtd])))

Showcase Report – Contoso By SpartaBI

     

[eversoncarvalho]

Here, I think I have a better explanation of what I need. How can I get the number of qtd sold by crm_medic without repeating the product of the order?

[SpartaBI]

@eversoncarvalho just replace the squ name in that column in my previous measure, but it seems that the other measure will also work the same. Have you tried it? Put it instead of the qty column in the visual

[SpartaBI]

@eversoncarvalho pat attension that I added a calculate now to the previous measure. Please use the new version. The old one worked for you becasue all the values were 1 but it will not give the right number in the total in other cases. So use the new version with calculate.