Add New Dynamically Updating Column To Data
Hi, I'm relatively new to using DAX in PowerBI and have turned to ChatGPT for some help but it's repeatedly giving me 2 solutions, neither of which work! The Data: I have just one data table of inbound calls into a call centre. Columns include: StartDateTime, Agent Name, Caller Number (among others). Inbound calls can be answered by different agents, so a caller number may appear 20 times on 20 different datetime occasssions and the caller may have spoken to 5 different agents. StartDateTime AgentName CallerNumber 02/09/2023 19:01 Bob Caller01 02/09/2023 19:15 Alice Caller01 07/09/2023 15:33 Alice Caller02 13/09/2023 12:04 Bob Caller01 I have created some calculated fields including: MinCallStartDateTime (returns the lower value of the date filter applied to the dashboard). MaxCallStartDateTime (returns the upper value of the date filter applied to the dashboard). MaxCallsMade (returns count of caller number based on date filters applied to dashboard). FirstCall (returns the datetime of the first call from the selected Caller Number based on the date filters applied to the dashboard). For Caller01 MinCallStartDateTime 01/09/2023 MaxCallStartDateTime 15/09/2023 MaxCallsMade 3 FirstCall 02/09/2023 19:01 What I want to achieve: 1) I want to know which agent answered the FirstCall "FirstCallAgent" - my dashboard currently returns the FirstCall PER agent (for each Caller Number Selected), I just care about who was the first agent to answer the very first inbound call (dependent on date filters applied to the dashboard). 02/09/2023 19:01 Bob 02/09/2023 19:15 Alice (this currently shows but I don't care about it) If possible it would be ideal to create a new column called "CallTally" to the table which can be updated to count each inbound call from that number dynamically to update based upon any date filters applied to the dashboard. StartDateTime AgentName CallerNumber CallTally - may change if date filter changes 02/09/2023 19:01 Bob Caller01 1 02/09/2023 19:15 Alice Caller01 2 07/09/2023 15:33 Alice Caller02 1 13/09/2023 12:04 Bob Caller01 3 2) "RepeatCallPerc" - The goal is to find out which agent is best at resolving customer problems meaning there will be fewer repeat calls / MaxCallsMade from Caller Number where one agent answered the very first call, relative to if another agent answered that first call (since a call can be answered by any agent). Bob 100% (100% of calls, where Bob answered the first call, called in at another time)(Caller01) Alice 0% (0% of calls, where Alice answered the first call, called in at another time)(Caller02) The best way I can think about doing this is to calculate: for each agent - SUM the total instances of CallTally= 1 (according to the new column created as part of step 1). Then divide this by the SUM of the total MaxCallsMade for caller numbers whereby the agent answered the FirstCall (regardless of which agents answered future calls) - does that make sense? Extra: 3) A third goal would be to isolate which agent answered the last call "LastCallAgent" from each caller number (meaning that agent was the one to resolve that query) - to be able to sum this up and see that calls handled by Agent X are more likely to be resolved (and therefore the customer is less likely to call in) compared to calls handled by Agent Y. Expressing this as a percentage for comparison reasons would also be useful "LastCallAgentPerc" = Count LastCallAgent / TotalLastCalls = SUM("LastCallAgent"). But again this would require updating the above table /column tallying inbound calls within the filtered date range. Bob 50% (50% calls Bob handled were "last calls" (Caller01) Alice 50% (50% of calls Alice handled were "last calls" (Caller02) Anyway, I have absolutely no idea how to begin this! Or if it's even possible. I would appreciate any insight and expertise you may be able to add to this. Or if you can add an easier way to think about this. Thanks.
Dax For TopNCustomers
Hey Everyone, I have two tables first 'Sales Data' from which I am focusing on columns 'Order Date', 'Shipping Mail Id' and 'Order Number' The second table is Calendar which is linked with the Sales Data table on the basis Calendar[ Date] and Sales Data[Order Date]. I want to know about the Top 5 customers' Order Count Grand total of every month. for example I want a table like this. YEAR MONTH ORDER COUNT 2023 JULY 88 2023 JUNE 108 2023 APRIL 107 And the order count come from another table: top 5 customer of month July Shipping Mail Id Order Count [email protected] xx [email protected] xx [email protected] xx [email protected] xx [email protected] xx grand total 88
Dispaly results for an item linked it's componentes on another columns
Hi everyone , I need some help to write DAX command , I have this table shown below : ITEM_ID QTY U_ID ACTION_DATE GROUP_ID CO_ID ACC_ADMIN.ITEMS NewQty ForecastedSales 033-048-060-3578 0.00347 Ahmed 4/1/2019 17:34 1008 5 0.00347 3.47 SP3237 0.004 Ahmed 4/1/2019 17:32 1008 5 0.004 4 SP2215 0.004 Ahmed 4/1/2019 17:32 1008 5 0.004 4 004-002-008-2459 0.5 Ahmed 4/1/2019 17:34 1008 5 0.5 500 019-35-005-1008 0.0416655 Ahmed 4/1/2019 17:29 1008 5 0.0416655 41.6655 019-35-005-1008 0.0416655 Ahmed 4/1/2019 17:29 1008 6 0.0416655 41.6655 033-048-060-3578 0.00347 Ahmed 4/1/2019 17:34 1008 6 0.00347 3.47 SP3237 0.004 Ahmed 4/1/2019 17:32 1008 6 0.004 4 SP2215 0.004 Ahmed 4/1/2019 17:32 1008 6 0.004 4 004-002-008-2459 0.5 Ahmed 4/1/2019 17:34 1008 6 0.5 500 033-048-008-2252 0.00174 Ahmed 5/28/2019 9:36 1103 5 0.00174 1.74 SP2215 0.0078 Ahmed 5/28/2019 9:37 1103 5 0.0078 7.8 004-002-008-3682 1 Ahmed 5/28/2019 9:36 1103 5 1 1000 011-037-020-3974 0.020833 Ahmed 5/28/2019 9:36 1103 5 0.020833 20.833 019-35-008-1103 0.020833 Ahmed 5/28/2019 9:36 1103 5 0.020833 20.833 033-048-008-2252 0.00174 Ahmed 5/28/2019 9:36 1103 6 0.00174 1.74 SP2215 0.0078 Ahmed 5/28/2019 9:37 1103 6 0.0078 7.8 004-002-008-3682 1 Ahmed 5/28/2019 9:36 1103 6 1 1000 019-35-008-1103 0.020833 Ahmed 5/28/2019 9:36 1103 6 0.020833 20.833 011-037-020-3974 0.020833 Ahmed 5/28/2019 9:36 1103 6 0.020833 20.833 036-000-000-3207 0.001 Ahmed 9/26/2018 0:00 SP2215 5 0.004 0.004 031-45-000-3198 0.08 Ahmed 1/12/2019 17:34 SP2215 5 0.004 0.32 001-013-000-2566 0.15 Ahmed 9/26/2018 0:00 SP2215 5 0.004 0.6 031-65-000-3192 0.77 Ahmed 8/4/2021 9:25 SP2215 5 0.004 3.08 036-000-000-3207 0.001 Ahmed 9/26/2018 0:00 SP2215 6 0.004 0.004 031-65-000-3192 0.77 Ahmed 8/4/2021 9:25 SP2215 6 0.004 3.08 001-013-000-2566 0.15 Ahmed 9/26/2018 0:00 SP2215 6 0.004 0.6 031-45-000-3198 0.08 Ahmed 1/12/2019 17:34 SP2215 6 0.004 0.32 036-000-000-3207 0.001 Ahmed 9/26/2018 0:00 SP3237 5 0.004 0.004 031-44-000-3194 0.77 Ahmed 9/26/2018 0:00 SP3237 5 0.004 3.08 001-019-000-3055 0.15 Ahmed 9/26/2018 0:00 SP3237 5 0.004 0.6 031-45-000-3198 0.08 Ahmed 2/26/2019 17:13 SP3237 5 0.004 0.32 036-000-000-3207 0.001 Ahmed 9/26/2018 0:00 SP3237 6 0.004 0.004 031-44-000-3194 0.77 Ahmed 9/26/2018 0:00 SP3237 6 0.004 3.08 001-019-000-3055 0.15 Ahmed 9/26/2018 0:00 SP3237 6 0.004 0.6 031-45-000-3198 0.08 Ahmed 2/26/2019 17:13 SP3237 6 0.004 0.32 The GROUP_ID which is ( 1008 ) linked with several ITEM_ID and one of the ITEM_ID ( SP2215 ) Can be also a GROUP_ID and have another ITEM_ID inside it. ITEM_ID QTY U_ID ACTION_DATE GROUP_ID CO_ID ACC_ADMIN.ITEMS NewQty ForecastedSales 036-000-000-3207 0.001 Ahmed 26-09-2018 0:00 SP2215 5 0.004 0.004 031-45-000-3198 0.08 Ahmed 12/1/2019 17:34 SP2215 5 0.004 0.32 001-013-000-2566 0.15 Ahmed 26-09-2018 0:00 SP2215 5 0.004 0.6 031-65-000-3192 0.77 Ahmed 4/8/2021 9:25 SP2215 5 0.004 3.08 036-000-000-3207 0.001 Ahmed 26-09-2018 0:00 SP2215 6 0.004 0.004 031-65-000-3192 0.77 Ahmed 4/8/2021 9:25 SP2215 6 0.004 3.08 001-013-000-2566 0.15 Ahmed 26-09-2018 0:00 SP2215 6 0.004 0.6 031-45-000-3198 0.08 Ahmed 12/1/2019 17:34 SP2215 6 0.004 0.32 So it's like a Tree. ( GROUP_ID which is1008 ) have an items started with numbers + one SP item which also contains items inside it ) The main issue is that the ( SP2215 ) ITEM_ID can be linked with more than one ( GROUP_ID ) , if you see on the table it's linked with item ID 1008 and 1103 and i tried to make it but i got an error says there's duplication. on the picture below , When i click on item 1008, it should show the ITEMS_ID related wiht it and if there was an SP or any item starts with a latter as ab ITEM_ID that means the SP can be also a GROUP_ID and it should show the items for it too on the Dashboard Expected result is shown on the Picture below : PPIX file on the link below : https://drive.google.com/file/d/1nKQrjZVeRrEVF9wdUR-5QCDrmgrfy8CQ/view?usp=sharing Excel File : https://docs.google.com/spreadsheets/d/1OJdfyjPgu--9xOp6om5xE6emY7mqn18g/edit?usp=sharing&ouid=103541566665752742076&rtpof=true&sd=true Thank you.
