cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tiago Regular Visitor
Regular Visitor

[Please HELP] Dax formula to summarize not working.

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.
 

 

Capturar.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: [Please HELP] Dax formula to summarize not working.

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
tiago Regular Visitor
Regular Visitor

Re: [HELP] Summarize Error

Bump! Please help!
Community Support Team
Community Support Team

Re: [Please HELP] Dax formula to summarize not working.

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tiago Regular Visitor
Regular Visitor

Re: [Please HELP] Dax formula to summarize not working.

Yuliana Gu,

 

 

 

 

Community Support Team
Community Support Team

Re: [Please HELP] Dax formula to summarize not working.

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.