summary tables
3 Topics
Help with creating a summary table
Hello, I am trying to create a summary table and could use some help! I am trying to calculate the amount of time an employee is in a particular pay plan (salary). For a data source, I am using a change log from an employee database. For privacy purposes, I have modified the data to cleanse it. However, below are some examples of what I am working with and what I am trying to accomplish. Scenario 1: ChangeRequestID EmployeeID PayPlanID Date JobTitleID 8886 400 12 12/20/2020 19 6360 400 11 12/22/2019 19 3713 400 9 5/27/2018 19 3163 400 8 2/4/2018 6 1972 400 7 2/5/2017 6 In this scenario, the employee has one record for each promotion, or move into the next pay plan. The outcome I am trying to get in this scenario is as follows: PayPlanID Duration (days) 12 709 11 364 9 574 8 112 7 364 I am wanting the duration column to represent the number of days the employee was in that pay plan. It's calculated by looking at the difference from that record until the next record, with the exception of the most recent record, which uses today's date for the calculation (since the employee is still in that pay plan). Senario 2: ChangeRequestID EmployeeID PayPlanID Date JobTitleID 13964 693 9 10/10/2021 4 12532 693 7 8/15/2021 5 7547 693 7 9/13/2020 6 6904 693 8 3/29/2020 4 5246 693 7 3/31/2019 9 In this scenario, the employee's play plan does not consistently increase. They took another job which had a lesser pay, and then eventually worked their way back up. In this scenario, I am looking for the same output as in scenario one. Below is the expected outcome: PayPlanID Duration (days) 9 415 8 168 7 756 I tried creating a table using SUMMARIZE(), grouping by EmployeeID and again by PayPlanID, however I wasn't sure what expressions I could use to calculate the duration in days. It would be much easier if I had a start & end date in the record, but I do not. Only the date the change was submitted. I would really appreciate any help or guidance on this! Thanks! ChrisSolved840Views0likes2Comments
Returns a summary table over the most common values between categories
Hi there! I´m trying to get a summary table over the most accessed reports between two or more categories (by function) in a "Log Events" table. The results will be helpful to create a cleaner network analysis chart. See the example below: User Function Report Log_Events A Marketing Report_A 5 A Marketing Report_B 6 A Marketing Report_C 2 B Account Report_D 1 B Account Report_A 8 C CS Report_B 9 D Finance Report_B 10 D Finance Report_A 2 E R&D Report_C 3 Expected outcome: Report Functions Log_Events Report_A 3 15 Report_B 3 25 Report_C 2 5 Report_D 1 1 I hope you can help me... thanks in advance!Solved1KViews0likes4Comments
Calling tables as variables in other variable
Hi, I have a fact table with the following columns: "Completion Date", "Email", "Specific Skill/Industry", and "Experience Level". A Microsoft form feeds an Excel table in SharePoint via Power Automate. Basically we have people do a survey of a self-evaluation on various skills. I am working on a measure wherein I was trying to build a summary table of the average scores for each Specific Skill/Industry across all respondents, then wanted to grab the minimum score and return the corresponding Specific Skill/Industry. So I built a summary table as a variable in the measure, but then I can't seem to get other variables within the measure to reference this summary table. Here is what I would like to do, but I get errors with it: Min_Skill = VAR Avg_Table = SUMMARIZE ( Survey_Table, Survey_Table[Specific Skill/Industry], "Avg_Score", AVERAGEX ( Survey_Table, [Latest_Score] ) ) VAR Min_Val = CALCULATE ( MIN ( Avg_Score ), ALL ( Avg_Table ) ) RETURN CALCULATE ( MIN ( Avg_Table[Specific Skill/Industry] ), Avg_Table[Avg_Score] = Min_Val ) (Latest_Score is a measure I created to capture only the most recent survey responses for each person, allowing for a person to respond multiple times to the survey. For the purpose of this question, you can treat this measure and the original "Experience Level" column the same.) The measure doesn't seem to recognize the internal links to the Avg_Table or to the Avg_Score summary column within Avg_Table. Is there any way to accomplish this without building a whole separate calculated table?Solved668Views0likes2Comments