Forum Discussion

Aucesar's avatar
Aucesar
Helper III
3 years ago
Solved

Distinct sum based on two columns

Hi Community,   Tried to count how many products the employee worked, sounds easy at the beggining but I cant count the second column.   Tried usuing DISTINCTCOUNTNOBLANK, but I can´t tell PBI ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Aucesar 

    You can refer to the following measure

    1.Create a table to diaplay the user

    2.Then create a measure

    ProductionBatchWorked = var a=SUMMARIZE(ALLSELECTED('Table'),[ProductionBatch],[Shift1])
var b=SUMMARIZE(ALLSELECTED('Table'),[ProductionBatch],[Shift2])
var c=SUMMARIZE(UNION(a,b),'Table'[Shift1],"Distinct",DISTINCTCOUNTNOBLANK('Table'[ProductionBatch]))
return MAXX(FILTER(c,[Shift1] in VALUES('Table 2'[Shift2])),[Distinct])

    Output

    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.

     

     

     

Dhairya's avatar
Dhairya
Solution Supplier
3 years ago

Hey Aucesar 
You have to transform your data first please follow the below steps

Input:


Step1: Open Power Query Editor and unpivot Shift1 and Shift2 columns, and click on close and apply
Output:


Step2: Now create following measure

S1 = 
CALCULATE(
    COUNTX(TableA,TableA[Product]), 
    ALLEXCEPT(TableA,TableA[Value])
)


Step3: Plot your expected columns you will get the following output

If this helps you then please mark my solution as accepted so that others can find it quickly while facing similar issue. Thank You!