Finding Multiple values in columns and rows and Calculating for each value as category

[fusiee_]

Hi Everyone, 

I have a fairly complicated task where I need help. I have a task where I have a data about each product data and and how long each department took to complete their task and then compare which department takes longer to complete its task. The issue is based on certain tasks and conditions same department name appears more than one columns or rows.. For example.

Product ID ( unique)	Commercial Data Task 1 ( date to complete)	Commercial Data Task 1( department)	Commercial Data Task2 ( date)	Commercial Data Task 2(department)	Technical Data task 1( days)	Technical DataData Task 1(department)
Apple	10 days	sales	3 days	marketing	10 days	sales
Orange	5 days	IT	36 days	sales	5	IT

So it looks like certain tasks have same department but different times.  So my task is to create a dax to find out how each department performs. in the view it will have lets say commerical data --> all the departments involved in this task and to see which department takes longer to complete their task when compared with other departments in total. The goal is make a visual where shows commerical data departments and comparing their each performance to find out which department takes longer to complete the task. 

I cant use unpivot cause its one table with over 20'000 rows and more than 100+ columns

I hope this is clear enough explaination. If you can help me that would be great. LOOKing forward to hearing from you. 

Best,

</>

[fusiee_]

Hi @Yolo ZU

Unfortunalty this is not possible cause the table has over 20'000 rows and over 100+ columns. Is there no other way to search and find these valuse and sum it up by department? 

Best,

Ahmet 

[v-xinruzhu-msft]

Hi  @fusiee_ 

According to the image you provided, this table needs to be reversed, this table cannot directly use DAX to achieve the desired effect, have you tried to transpose the table in Power Query?

 Please refer to the following links:

Unpivot columns - Power Query | Microsoft Learn

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}