Exclude a row from a dimension though include in the value in the Total

[MikeJones]

Below shows my prefered outcome

Current Table	Planned Jobs		Desired Table	Planned Jobs
Total	400		Total	400
Employer 1	100		Employer 1	100
Employer 2	50		Employer 2	50
Direct	250

To get my desired table I have used for the dimension =if(Employer<>'Direct',Employer) and the box to include null values is empty.

Measure uses If(Dimensionality() = 0, Sum({<JobType={'XX'}>} Jobs),
Sum({<JobType={'XX'}, Employer-={'Direct'}>} Jobs))

Using the above the total shows as 150.  When include null vales, the total is correct though I get a dash in the Employer with a value of 0.

How can I get the Desired Table to show in Qlik

[marcus_sommer]

What happens if you just used Employee as a native dimension - with your shown expression branching the calculation in regard to the object-dimensionality? 

[MikeJones]

Thanks for response Marcus.  What do you mean use Employer  as a native dimension?

I don't understand what you are meaning with regard to o the object-dimensionality.

[EmmaMcAlister · Accepted solution]

Hi Mike,

Try changing your dimension from =if(Employer<>'Direct', Employer)  to Employer
and your measure to IF(ROWNO() = 0, Sum({$<JobType={'XX'}>} Jobs),
Sum({$<JobType={'XX'}, Employer-={'Direct'}>} Jobs)).

Then you should be able to set a limitation on your Employer field to only show rows where your value is >0.

Let me know if it works!

[MikeJones]

Hi Emma, thanks for your help, it works wonderfully.