[Please HELP] Dax formula to summarize not working.

[tiago]

Hi there,

I am fairly new no DAX language and I am trying to learn a bit.

I’ve built a table with the following sales data: Product ID, Product Description, Sold Items, Stock. I am filtering just to check the products sold on the past 10 days, so I can see what the best sellers are and their current stock.

I am using 3 SQL tables for this (I’am using direct query to extract the data, so I cannot build a new table).

The 3 tables are.

ESTOQUE_PRODUTO (It contains the stock of all products)
LOJA_VENDA_PRODUTO (It has all sold products)
PRODUTOS (It has the product id description)

All tables are correctly related.

My problem is that the PRODUCT ID is not unique. A same product ID can repeat it self because there are different colors. In this Table I wanted to summarize the Product ID.

At the moment I have something like this
Produto = ProductID
Descrição = Description
Venda = Sales
Estoque = Stock


As you can notice the product 21804075 is showing multiple times because it has multiple colors. I wanted for it to show on this table as a single unique value with all sales data and stock aggregated.
 

So the correct output for this item should be:

 Produto          Descrição         Venda        Estoque
21804075         Blusa               150             15
 
I’ve tried to create a new measure on the LOJA_VENDA_PRODUTO table to sum all products sales with the same product id)

Total Sales =
SUMMARIZE(LOJA_VENDA_PRODUTO,
LOJA_VENDA_PRODUTO[PRODUTO],
"Total Sales",
SUM(LOJA_VENDA_PRODUTO[QTDE])
)
 
But I get an error:
The expression references several columns. You can not convert multiple columns to a scalar value.
 
What am I doing wrong? I’ve researched a lot before creating this post. I saw that some people create a new table in order to aggragate all data but since I use direct query, I cannot create a new table.
 
Any help is appreciated.
Thanks in Advance,
Regards,
Tiago

[v-yulgu-msft]

Hi @tiago,

As SUMMARIZE() function returns a table rather than a scalar value, you cannot directly use it to calculate total values in a measure.

Please try this measure:

Total Sales =
CALCULATE (
    SUM ( LOJA_VENDA_PRODUTO[QTDE] ),
    ALLEXCEPT ( LOJA_VENDA_PRODUTO, LOJA_VENDA_PRODUTO[PRODUTO] )
)

Best regards,

Yuliana Gu

[tiago]

Yuliana Gu,

Thank you for the reply. I was able to create the measure but when i try to add it to the table it takes a while trying to load the calculated field into the table and then I recieve an error (there is not enough memory to complete this operation).

When you tried to help me I saw that the post, for some reason was a mess.

Please let me know if there is another way to approach this.

Any help is appreciated.

Thanks

[v-yulgu-msft · Accepted solution]

Hi @tiago,

Did you load huge number of data into desktop? This might cause memory issue when applying something complex. Please refer to suggestion mentioned in this thread to see if it works in your scenario.

Regards,

Yuliana Gu

[tiago]

Bump! Please help!