Is there another function as an alternative to calculate + allexcept

[Applicable88]

Hello,

here is my sample table:

Date		Sales		Categories
01.01.2022		123		Shoes
02.01.2022		55		Shoes
03.01.2022		700		Machine
04.01.2022		900		Computer
05.01.2022		900		Computer

I need a measure for calculating the sum of each categories:

Date		Sales		Categories		Measure
01.01.2022		123		Shoes		178
02.01.2022		55		Shoes		178
03.01.2022		700		Machine		700
04.01.2022		900		Computer		1800
05.01.2022		900		Computer		1800

Mostly I seen PBI-users using a typical calculate with an modifier like Allexcept to get that value: 

Measure = Calculate ( SUM ( 'Salestable' [Sales], Allexcept ( 'Salestable', [Categories]))

Is there a alternative way to get to the same result without the usage of calculate? For example with x-aggregated function or a table variable inside a measure?

Thank you very much in advance.

Best. 

[ValtteriN · Accepted solution]

Hi,

You can use SUMX to get he result without CALCULATE. Something like this: 

Measure 12 = SUMX(ALLEXCEPT( 'Salestable', [Categories]),'Salestable' [Sales])

[v-cazheng-msft · Accepted solution]

Hi @Applicable88 

Please try this Measure.

TotalByCategories =

VAR _Table =

    FILTER ( ALL ( 'Table' ), 'Table'[Categories] = MAX ( 'Table'[Categories] ) )

RETURN

    SUMX ( _Table, 'Table'[Sales] )

Then, the result will look like this.

Also, attached the pbix file as the reference.

Best Regards,

Community Support Team _ Caiyun

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Applicable88]

Hi @v-cazheng-msft,

thank you for the *pbix - file. 

I have a question: 

If I use the table variable inside you measure to create a calculated table:

When I put this as a table variable inside a measure like you did,  shouldn't I get "178" only for shoes? 

Since you saying MAX[Categories] it should only return "shoes" since its Max in alphabet. 

Why would I get this outcome that following outcome:

Hope I was clear.

Thanks. 

[Applicable88]

@ValtteriN , thanks! That was what I was looking for. Seeing that means also it can be used as a filter table inside a measure?

Measure = 

var _Table = Allexcept ( 'Salestable', Categories)
return

Sumx (_'Salestable', [Sales]) 

Best. 

[ValtteriN · Accepted solution]

Hi,

You can use SUMX to get he result without CALCULATE. Something like this: 

Measure 12 = SUMX(ALLEXCEPT( 'Salestable', [Categories]),'Salestable' [Sales])