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 to merge shift1+shift2 and give me a single result.
 
Also tried some suggestions with sumx but the data is a string, and I got errors.
 
Thanks in advance for any help...

 

   Desire results
ProductShift1Shift2NameProductsWorked
aWadeBrianWade2
bDaveRobertoDave3
cSethRamonSeth4
dBrian Brian3
eRobertoRileyRoberto2
fRamonGilbertRamon3
gJorgeGilbertJorge1
h DanGilbert5
iBrianWadeRiley1
jRobertoDaveDan1
kRamonSeth  
lRamonBrian  
m Dave  
nGilbertRamon  
oGilbertSeth  
pGilbertSeth  
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.

     

     

     

6 Replies

  • 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!

    • Aucesar's avatar
      Aucesar
      Helper III
      3 years ago

      Hi Ahmedx / Dhairya thanks so far, as the users are never happy, how to accomplish this new column?
      Thanks

          Desire results 
      ProductionBatchProductShift1Shift2NameProductsWorkedProductionBatchWorked
      XX01aWadeBrianWade21
      XX01bDaveRobertoDave33
      XX01cSethRamonSeth43
      XX01dBrianWadeBrian32
      XX02eRobertoRileyRoberto22
      XX02fRamonGilbertRamon33
      XX02gJorgeGilbertJorge11
      XX02h DanGilbert53
      XX02iBrianRamonRiley11
      XX02jRobertoDaveDan11
      XX02kRamonBrian   
      XX03lRamonSeth   
      XX03m Dave   
      XX03nGilbertRamon   
      XX03oGilbertSeth   
      XX03pGilbertSeth   



      • Anonymous's avatar
        Anonymous
        Not applicable
        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.