ALL Function not working the same way in measure than in calculated column

[costadom]

Hi All,

I'm trying to use a calculated column instead of a measure in order to optimize my PBI file since I have a lot of calculations performed.

I have a measure that is working fine but when I'm trying to replicate it in a calculated column, it's not working.

The idea is the have a sum of volume at a brand level.

Below print screen for better understanding and below formula used in measure that is working

PS NEW YTD all Sizes = CALCULATE(SUM('Product Sales NEW'[YTD NEW]),ALL('Brand Code Size'[Size Code]))

Thank in advance for your help

Jerome

[KHorseman]

First of all, adding a column instead of a measure usually does the opposite of optimizing your file.

If you're adding that column to the 'Product Sales NEW' table, it will calculate the sum of sales for each row in that table. The ALL statement refers to a different table, so the row context will still be that one row of the table. You would have to use ALL('Product Sales NEW') to remove the row context from the table you're on.

But you still probably should not add a column to accomplish this. That will tend to just make your file bigger and slower.

[costadom]

Thanks Khorseman for your quick reply.

I have tried to have all the fields within the same table but it seems not to work.

See above print screen.

Let me know if you see something wrong.

Thanks for your help!

Jerome

[mattbrice]

Sorry for repetitiveness, but I agree with @KHorseman.  Usually you are better off with a measure than a calculated column. 

Also, if the column is in 'Product Sales NEW' then the formula to try is:

column =
CALCULATE (
    SUM ( 'Product Sales NEW'[YTD NEW] ),
    FILTER (
        ALL ( 'Product Sales NEW'),
        'Product Sales NEW'[Brand Size Code]
            = EARLIER ( 'Product Sales NEW'[Brand Size Code] )
    )
)

Although I made up the column 'Brand Size Code'. You'll need to change that to whatever it really is.

[KHorseman]

^Kudoed for the formula, not just for agreeing with me.

[costadom]

Still not working,

See below

For your information, I'm trying columns only because I have tried all my calculation with measure and the system is not able to calculate all my formula at once (it's using more than 15GB of ram for your information when I'm doing my calculation)

This is due to the fact that my calculation have data reading and linling mu;tiple tables and each table contains more than 100K of rows...

My idea of using calculated columns is to cut the processing time meaning one part of the calculation will done already and stored in the columns and the other part of the calculation will be done by measure.

Hope this makes sense.

Thank you though for your help, it's very appreciated

[mattbrice]

If you open up the data model and look at the calculated column, does it return the expected results?  As a calculated column if I understand what you are doing it should.

[costadom]

Unfortunalty it's not good also in the data model.

But I have found his below

Since in my data I have another level, which is the state, PBI is doing the sum only at the brand level without considering the state.

We can clearly see it on the example above.

I think I'm missing a condition on the formula but not sure what...

[Eric_Zhang]

---

@costadom wrote:

Unfortunalty it's not good also in the data model.

But I have found his below

Since in my data I have another level, which is the state, PBI is doing the sum only at the brand level without considering the state.

We can clearly see it on the example above.

I think I'm missing a condition on the formula but not sure what...

---

To take the state into account as well, try to add an extra filter.

column =
CALCULATE (
    SUM ( 'Product Sales NEW'[YTD NEW] ),
    FILTER (
        ALL ( 'Product Sales NEW' ),
        'Product Sales NEW'[Brand Size Code]
            = EARLIER ( 'Product Sales NEW'[Brand Size Code] )
                && 'Product Sales NEW'[NewColumn.MarketName]
            = EARLIER ( 'Product Sales NEW'[NewColumn.MarketName] )
    )
)