sum column of variable table power bi

[Anonymous]

Hi All,

I'm trying to cumulate values from values from calculated (dax)-table. I've tried to use SUMX, but it's not working. Does someone know how to sum all these values? As displayed in Excel?

Let me know, thanks in advance.

 

[v-deddai1-msft]

Hi @Anonymous ,

In my opinion, your formula is in a little mess. Why did you need to create a temporary table in your measure. Just use the following measure:

Measure =
VAR _stockcount =
    CALCULATE (
        SUM ( 'WMS'[Mutatie Aantal] ),
        FILTER ( ALL ( Dates ), Dates[Date] ) < MAX ( Dates[Date] )
    )
RETURN
    IF ( _stockcount = BLANK (), 0, _stockcount )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Dear @v-deddai1-msft , 
Thank you for your reply. I've tried you're code but it shows an error: "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.". Hence, I've adapted the code to:

Test = 

VAR _stockcount = 

 CALCULATE (

 SUM ( 'WMS Data'[Mutatie Aantal] ),

 FILTER ( ALL ( Dates ), Dates[Date] < MAX ( Dates[Date] ))

 )

RETURN

 IF ( _stockcount = BLANK (), 0, _stockcount )

Let me know if this did alter the functionality of the formula.

In regards to the table, I've created the temporary table to see the stock level on a daily basis, hoping to be able to SUM the column later (like I do with a normal column). Looking to your code, I get the same results for the daily basis. However, I'm also not able to have a cumulative value if I SUM each day (red).

[v-deddai1-msft]

Hi @Anonymous ,

Would you please share me the pbix file? There could be some external filter on the visual.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[MFelix]

Hi @Anonymous ,

I can see you have more information that the columns that are presented and that you are using them on your measure can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

Also do you want to have a cumulative value on the column or only on the total?

[Anonymous]

Dear @MFelix ,

Thank you for the offer, I'll send you a link in a private message. The main goal is to calculate the average number of items per day in our warehouse (if stock > 0). Therefore I need the total. 

The mockup data contains only two article numbers. In reality we have around 80.000. I want to be able to see the average stock per day for every single article. Let me know if you need a better way to accomplish this.

Kind regards,

TGB

[MFelix · Accepted solution]

Hi @Anonymous ,

Has discussed and taking into account that you already have a calculation line by line that is correct and so that you don't overthink the calculation just create a measure SUMX based on the first measure something similar to:

Test of YTD = SUMX(Dates, [Stock Start])

Regards

Has you can see below the result is correct, so insted of using the Omloopsnelheid Product YTD2 measure use the Test one.