topic Re: Category wise sum in table in DAX Commands and Tips

Category wise sum in table

rajuctech — Fri, 24 Jun 2022 05:55:28 GMT

Hi all,

i have a below query sum up the category wise value, but the DAX is not giving the correct results.

L2_L5_Sum =
var P=SELECTEDVALUE('Calander'[Year])
var a=CALCULATE(SUM('tbl'[Value]),'tbl'[Category]="L2")
var b=CALCULATE(SUM('tbl'[Value]),'tbl[ategory]="L3")
var c=CALCULATE(SUM('tbl'[Value]),'tbl'[Category]="L5",'Calander'[Year]=P)
var x=a+b+c
Return
x

Any suggestion would be fine.

Re: Category wise sum in table

NilR — Fri, 24 Jun 2022 06:03:56 GMT

try sumx
var a=CALCULATE(SUMX('tbl'[Category], [Values]),'tbl'[Category]="L2")

Re: Category wise sum in table

rajuctech — Fri, 24 Jun 2022 06:45:14 GMT

the above one is not working, sumx(table and Expression only)

Re: Category wise sum in table

NilR — Fri, 24 Jun 2022 16:16:23 GMT

try this then:

CALCULATE ( SUM( 'Table '[ValueS] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Category ] = MAX ( 'Table'[Category ] && [Category ="L2") ) )

Re: Category wise sum in table

v-cazheng-msft — Tue, 28 Jun 2022 01:46:01 GMT

Hi rajuctech,

You may try this Measure.

SumOfVal = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category] IN {"L2","L3","L4","L5"}))

The result looks like this.

Also, attached the pbix file as reference.

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 let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

Re: Category wise sum in table

rajuctech — Wed, 29 Jun 2022 04:15:07 GMT

Thank you very much msft, it's working fine.