Table as a variable doesn't work

[Veigar]

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?

[Veigar]

@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.
Thanks

[selimovd · Accepted solution]

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:

RETURN – DAX Guide

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

[Veigar]

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.

[selimovd]

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 regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Veigar]

Hi @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]

Hey @Veigar ,

what is a static table?

And why can't you post the measure?

Best regards

Denis