Forum Discussion

Vivek26's avatar
Vivek26
Icon for Advocate I rankAdvocate I
2 years ago
Solved

Need help with a Measure without creating calculated columns

I have a scenario where I need to get the sum of specific rows if they follow a particular path. 

We need to calculate the sum of rows(column Hours) with DEPT_NAME "HR" only if it also went through the DEPT "OPS" for the same order.

For example we should consider the Orderid 1 and 3 because those order had both HR and OPS while 2 is not considered because it didn't had OPS.

Now we also need to calculate the sum of hours for these subset of orders that fullfill the above criteria ("HR" with OPS) .

Sample Data and snapshow below , Can you please help me with this ?

IDDateOrder IDDEPT NAMEHours
122-12-20231OPS10
224-12-20231FIN12
329-12-20231HR14
411-12-20232FIN20
520-12-20232HR30
614-12-20233FIN30
720-12-20233OPS40
820-12-20233HR50

 

 

  • Dangar332's avatar
    Dangar332
    2 years ago

    hi, Vivek26 

    try below measure 

    Measure 2 = 
CALCULATE(
     SUM('Table (3)'[hours]),
     FILTER(
        'Table (3)',
        'Table (3)'[dept name] in {"hr","ops"} && 
        'Table (3)'[order id]=MAX('Table (3)'[order id])
    )
)

     

     

    code for month-year

     

2 Replies

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    hi, Vivek26 

    try below measure 

    Measure 2 = 
CALCULATE(
     SUM('Table (3)'[hours]),
     FILTER(
        'Table (3)',
        'Table (3)'[dept name] in {"hr","ops"} && 
        'Table (3)'[order id]=MAX('Table (3)'[order id])
    )
)

     

     

    code for month-year

     

  • Kishore_Kadhir's avatar
    Kishore_Kadhir
    Icon for Resolver II rankResolver II
    2 years ago
    Hi Vivek26 ,

     

    See if the below measure works:
    TotalHourswithHRandOPS =
    var concatenation = CONCATENATEX(VALUES(Dept[DeptName]), Dept[DeptName], ",")
    var output = SUMMARIZE(Dept, Dept[OrderID], "Dept", SWITCH(TRUE(),
                                                        CONTAINSSTRING(concatenation, "OPS") &&
                                                        CONTAINSSTRING(concatenation, "HR"), "Yes"))
    RETURN
    SUMX(FILTER(output, [Dept] <> BLANK()), CALCULATE(SUM(Dept[Hours]), FILTER(Dept, Dept[DeptName] IN {"HR", "OPS"})))
     

     


     

    Thanks,

    Kishore