Forum Discussion
Table as a variable doesn't work
Hi everyone,
I have created a table variable:
Measure_ =
Var _table =
SUMMARIZE(
CALCULATETABLE(
STOCK,
FILTER(STOCK, RELATED(_Outliers[Outlier cost]) <> "Outlier"),
FILTER(STOCK, STOCK[date] >= DATE(2020, 11, 22))
),
STOCK[Itm_ID],
"Average", CALCULATE(AVERAGE(STOCK[Quantity]), FILTER(STOCK, STOCK[date] >= DATE(2020, 11, 22)))
)Now i want to calculate the cumulated sum by doing this, in order to save in another variable table:
var _table1 = SUMMARIZE(_table,
STOCK[Itm_ID],
"column",
CALCULATE(SUM(_table[Average], ALL(_table), [Average] >= EARLIER([Average]))
)) But every approach I use is useless.
May someone expain me how can i do this?
Hey Veigar ,
you can filter a calculated table. I don't see why this should not be possible.
The measures you posted in the initial post cannot work. You put variables (VAR) but there is no RETURN. That just doesn't work. Check out the syntax:
But as far as I understand that measure is not working at the moment, right?
I realized you want to do some ABC analysis. Check the article on dax patterns, maybe that will help you:
ABC classification – DAX Patterns
Best regards
Denis
6 Replies
Hey Veigar ,
can you post the whole measure? When you split it that leaves room for interpretation.
Also the last SUM is missing a closing bracket "CALCULATE(SUM(_table[Average]), ALL(_table), ..."
Then what do you return? When you use variables you have to put a RETURN at the end and then tell the measure what you want to return. For example:
YoY% = VAR Sales = SUM(SalesTable[SalesAmount]) VAR SalesLastYear = CALCULATE ( SUM ( SalesTable[SalesAmount] ), SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) ) RETURN if(Sales, DIVIDE(Sales – SalesLastYear, Sales))If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicHi selimovd, thanks for the answer.
I had a static table and so i cannot use any filter.
So i tried to build this measure. The aim of this measure is to calculate the quantity of the items in "A CLASS". (ABC Analysis).
I wanted to calculate the numbers of A and i tried to add these columns: Cumulated stock, Cumulated percentage, ABC value (Which will be a IF function).
selimovd , A static table is a calculated table (at least for me). The kind of tables that you can find on the right of the screen.
I posted all of the measure i wrote, i'm only stuck because i cannot add another column to the first variable. If i understand this, I can finish the measure by my self.
ThanksHey Veigar ,
you can filter a calculated table. I don't see why this should not be possible.
The measures you posted in the initial post cannot work. You put variables (VAR) but there is no RETURN. That just doesn't work. Check out the syntax:
But as far as I understand that measure is not working at the moment, right?
I realized you want to do some ABC analysis. Check the article on dax patterns, maybe that will help you:
ABC classification – DAX Patterns
Best regards
Denis
Hi selimovd ,
I know that i have to put the return, but, as you said, the measure was not completed.
Second, I can filter a calculated table, but i wanted to filter the rows in which will be calculated the ABC.
I give you an example:
I have 100 items and i created a calculated table for the ABC for all of the items. Now if i want to calculate the ABC of only the items that are, for example, red, the filter will select the rows where the item has that quality (is red), but the ABC analysis is done on all the items and then filter.
My aim is to filter first, and then apply the ABC Analysis.
I found the same link you found about the ABC and then I realized my approach was totally wrong, also because I was not so sure what to return.
So i will try to implement it in my code.
Thank you very much, you were fundamental.
