If jobs match, sum cost types

[Serdet]

Hi All,

I have a dataset similar to the below

Job	Unit Rate or Day Work	Cost
1	Unit Rate	£10
2	Day Work	£50
1	Day Work	£100
4	Unit Rate	£1000

I want to create a two measures measures

Measure 1 - checks to see if the same job has unit rate and day work cost allocated then creates a count of how many times this happens

Measure 2 - checks to see if the same job has unit rate and day work cost allocated then sums up the 'Cost' for how many times this happens 

The above example would return the below outputsl

Measure 1 = 1

Measure 2 = £110

Please can someone help me with the DAX?

[v-yalanwu-msft]

Hi, @Serdet ;

Try it.

Measure 1 = 
var _a=SUMMARIZE( FILTER(ALL('Table'),[Unit Rate or Day Work]="Unit rate"),[Job])
var _b=SUMMARIZE( FILTER(ALL('Table'),[Unit Rate or Day Work]="Day work"),[Job])
var  _c=DISTINCT( INTERSECT(_a,_b))
return COUNTROWS(_c)

Measure 2 = 
var _a=SUMMARIZE( FILTER(ALL('Table'),[Unit Rate or Day Work]="Unit rate"),[Job])
var _b=SUMMARIZE( FILTER(ALL('Table'),[Unit Rate or Day Work]="Day work"),[Job])
return  SUMX(FILTER(ALL('Table'),[Job] in INTERSECT(_a,_b)),[Cost])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Serdet ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Serdet]

Thanks for the reponse.

Please see below example

Measure 1 - If jobs are the same and have unit rate and day work, make a count

                    In the above example, job 1 has a row for unit rate and day work, this would count as 1

Measure 2 - If jobs are the same and have unit rate and day work, sum the cost

                    In the above example, job 1 has a row for unit rate and day work, this would sum up the cost                         for each of these, totaling to £110

Hope this makes sense.

[amitchandak]

@Serdet , Try two measures like

countx(filter(summarize(Table, Table[Name], "_1", countx(filter(Table, Table[ Unit Rate or Day Work] = "Unit rate"),[Job])
, "_2", countx(filter(Table, Table[ Unit Rate or Day Work] = " Day work"),[Job]) )
not(isblank(_1)) && not(isblank(_2)) && [_1] >0 && [_2]>0), [Name] )

Sumx(filter(summarize(Table, Table[Name], "_1", countx(filter(Table, Table[ Unit Rate or Day Work] = "Unit rate"),[Job])
, "_2", countx(filter(Table, Table[ Unit Rate or Day Work] = " Day work"),[Job]), "_Sum", sum(Table[Cost]) )
not(isblank(_1)) && not(isblank(_2)) && [_1] >0 && [_2]>0), [Cost] )

[Serdet]

Hi, 

Thanks for the clarification, the CountX expression works perfeclty but I am having troubles with the Sumx expression. The DAX is not allowing me to put the last [Cost] into the expression.

Please see below.

[Samarth_18]

@Serdet , You have some missing closing brackets. Please use it like below:-

SUMX (
    FILTER (
        SUMMARIZE (
            Employee,
            Employee[Employee Name],
            "_1",
                COUNTX (
                    FILTER ( Employee, Employee[Day Work Indicator] = "Unit rate" ),
                    [Job]
                ),
            "_2",
                COUNTX (
                    FILTER ( Employee, Employee[Day Work Indicator] = " Day work" ),
                    [Job]
                ),
            "_Sum", SUM ( Employee[UP Cost] )
        ),
        NOT ( ISBLANK ( _1 ) )
            && NOT ( ISBLANK ( _2 ) )
                && [_1] > 0
                && [_2] > 0
    ),
    [UP Cost]
)

[Serdet]

Hi,

I am still being told [UP Cost] cannot be used in this expression. 

[Samarth_18 · Accepted solution]

@Serdet , try this:-

SUMX (
    FILTER (
        SUMMARIZE (
            Employee,
            Employee[Employee Name],
            "_1",
                COUNTX (
                    FILTER ( Employee, Employee[Day Work Indicator] = "Unit rate" ),
                    [Job]
                ),
            "_2",
                COUNTX (
                    FILTER ( Employee, Employee[Day Work Indicator] = " Day work" ),
                    [Job]
                ),
            "UP Cost", SUM ( Employee[UP Cost] )
        ),
        NOT ( ISBLANK ( _1 ) )
            && NOT ( ISBLANK ( _2 ) )
                && [_1] > 0
                && [_2] > 0
    ),
    [UP Cost]
)