Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |