SUMX with filter

[akhmad_danial]

Hello All,

I'am trying to do something, I thought would be simple but its not working.

I want to know how to get sub total and total: 

 

I tried to use sumx and filter, but its not working:

Sub Total =

VAR Permanent = CALCULATE(SUMX(EMPLOYEE,[Count Female]+[Count Male]),EMPLOYEE[EMPL_STATUS#1] = "Permanent")

VAR Contract = CALCULATE(SUMX(EMPLOYEE,[Count Female]+[Count Male]),EMPLOYEE[EMPL_STATUS#1] = "Contract")

Return

CALCULATE(Permanent+Contract)

What DAX should i use to calculate this?

Thanks for any help you can give

[Anonymous · Accepted solution]

Hi  @akhmad_danial ,

As under "Contract",there is no value,so if you wanna show as you posted,you'd better create a table as below first:

Table 2 = SUMMARIZE('Table','Table'[Status],'Table'[Custom])

Then create a measure  as below:

Measure 2 = IF(ISINSCOPE('Table'[Gender]),COUNT('Table'[Gender]),CALCULATE(COUNT('Table'[Gender]),FILTER(ALL('Table 2'),'Table 2'[Custom]<=MAX('Table 2'[Custom]))))

And you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[akhmad_danial]

Thanks @Anonymous  it worked.

Regards,

Akhmad

[harshnathani]

Hi @akhmad_danial ,

Can you please share some sample data and your actual table snapsot?

Understand this is a visualzation snapshot which you have posted.

Regards,

HN

[akhmad_danial]

Hi @harshnathani ,

Thank you for the respons.

This is snapsot table,

i think when i sort the data employement status, the value has changed.

Hope this helps.

Thank you.

[Anonymous]

Hi @akhmad_danial ，

Based on my test,in matrix ,you can only achieve the below result.

For the field,settings should be as below:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[akhmad_danial]

Hi @Anonymous ,

Thank you for your response.

I already to set row sub total before but the result not as expected.

I want to calculate (like this, click here) :

Subtotal =  Permanent (Male + Female) + Dir (Male + Female) + Expat (Male + Female)

Total = Subtotal + Contract (Male + Female) 

This is pbix file

Regards,

Akhmad