Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Customization to be achieved: (n) + Dynamic TOP(m) + Others

Hi,

 

I have not been able to implement one of the common functionality in Power BI.

Customization to be achieved: (n) + TOP(m) + Others

 

n = Static (Emp3, Emp4 and Emp6)
m = Variable (TOP 1 from remaining Emp). Dynamically calculated based on slicer value
Others = All remaining Emp to be grouped as "Others"

 

Input:

TableName: EmpTable

EmpIDEmpNameWorkLocationLogDate
1Emp1Loc11-Apr-2020
1Emp1Loc22-Apr-2020
1Emp1Loc11-Apr-2020
1Emp1Loc33-Apr-2020
1Emp1Loc41-Apr-2020
2Emp2Loc21-Apr-2020
2Emp2Loc32-Apr-2020
2Emp2Loc43-Apr-2020
2Emp2Loc33-Apr-2020
2Emp2Loc33-Apr-2020
3Emp3Loc21-Apr-2020
3Emp3Loc22-Apr-2020
3Emp3Loc22-Apr-2020
4Emp4Loc31-Apr-2020
4Emp4Loc12-Apr-2020
5Emp5Loc22-Apr-2020
5Emp5Loc33-Apr-2020
5Emp5Loc13-Apr-2020
6Emp6Loc21-Apr-2020
6Emp6Loc23-Apr-2020
7Emp7Loc41-Apr-2020

 

Output:

Need to display in "Clustered Bar Chart", EmpNames and Distinct count of EmpID based on slicer value in WorkLocation and LogDate

 

LogDate2-Apr-2020 LogDate4/1/2020,4/2/2020 LogDate1-Apr-2020
WorkLocationLoc2, Loc3 WorkLocationLoc2, Loc3 WorkLocationAll
        
EmpNameCount of Distinct EmpID EmpNameCount of Distinct EmpID EmpNameCount of Distinct EmpID
Emp21 Emp22 Emp12
Emp31 Emp32 Emp31
Others2 Emp61 Emp61
   Emp41 Emp41
   Others2 Others2

 

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ，

     

    Take Apr 2-2020 for example:

    Location 2,Location 3,

    Then you would see:

    So the distinct EmpID should be:

    EmpNameCount of Distinct EmpID  
    Emp21  
    Emp31  
    Others2  

    It is different from your expected output,so I'm guessing whether I have misunderstood your point,pls correct me.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hey Kelly,

       

      Thanks for reaching out.

       

      You have understood it correctly. I'll update the excepted o/p. 

       

      Primary problem statement is still to be achieved dynamically.

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Sorry but I still not quite get your point.See below:

    LogDate2-Apr-2020 LogDate4/1/2020,4/2/2020 LogDate1-Apr-2020
    WorkLocationLoc2, Loc3 WorkLocationLoc2, Loc3 WorkLocationAll
            
    EmpNameCount of Distinct EmpID EmpNameCount of Distinct EmpID EmpNameCount of Distinct EmpID
    Emp21--Why top1 is Emp2 Emp22 Emp12
    Emp31 Emp32---I guess here should be 1?? Emp31
    Others2 Emp61 Emp61
       Emp41 Emp41
       Others2 Others2

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       Hey Kelly,

       

      Here is the clarification:

       

      Case 1: In case of tie (highest count), each EmpName is to be made visible. Remaining to be grouped as "Others"

       

      So in example 1, the o/p shall change as below:

       

      EmpNameCount of Distinct Emp ID
      Emp11
      Emp21
      Emp31
      Emp51

       

      For example 2, the o/p is correct. There are 2 records each for date 1st and 2nd April 2020

      EmpIDEmpNameWorkLocationLogDate
      3Emp3Loc21-Apr-20
      3Emp3Loc22-Apr-20

       

      I hope this clarifies. 

       

      Thank you

      Akash