Forum Discussion

Aucesar's avatar
Aucesar
Helper III
3 years ago
Solved

DAX Calculation Help

Hi Community another day, another challenge 🙂

 

Need to acomplish this calculation...thanks for any help in advance!

 

DateMainWorkerAssistantTotalItensDefectoiTens%DefectoDesired Result = Sum all % results when the worker is Main or Assistant%Total
day1employee1employee210055%Employee110%
day1employee3employee41001010%Employee210%
day1employee5employee61001515%Employee330%
day1employee7employee810000%Employee435%
day2employee2employee310055%Employee535%
day2employee4employee51001010%Employee635%
day2employee6employee71001515%Employee725%
day2employee8employee110000%Employee80%
day3employee6employee110055%  
day3employee7employee51001010%  
day3employee3employee41001515%  
day3employee2employee810000%  
Need Help
  • vicky_'s avatar
    vicky_
    3 years ago

    Calculation = 
var helperTable = UNION(
    SELECTCOLUMNS(ALL('Table'), "workers", 'Table'[MainWorker], "defecto", 'Table'[%Defecto]), 
    SELECTCOLUMNS(ALL('Table'), "workers", 'Table'[Assistant], "defecto", 'Table'[%Defecto])
)
return SUMX(FILTER(helperTable, [workers] = SELECTEDVALUE('Table'[MainWorker])), [defecto])

    Hope this helps.

3 Replies

  • vicky_'s avatar
    vicky_
    Super User
    3 years ago

    Calculation = 
var helperTable = UNION(
    SELECTCOLUMNS(ALL('Table'), "workers", 'Table'[MainWorker], "defecto", 'Table'[%Defecto]), 
    SELECTCOLUMNS(ALL('Table'), "workers", 'Table'[Assistant], "defecto", 'Table'[%Defecto])
)
return SUMX(FILTER(helperTable, [workers] = SELECTEDVALUE('Table'[MainWorker])), [defecto])

    Hope this helps.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    In the Query Editor, select all columns other than Main Worked and Assistant, right click and select Unpivot Other columns.  Rename the Atribute column as Employees.  Drag the Employees column to the visual and write this measure

    Measure = sum(Data[%Defecto])

    Hope this helps.