Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, fellows.
I have this table and these formulas below:
Avg Ticket =
DIVIDE(SUM(Sales[Sales Amount]);[Distinct customers];0)
Distinct customers =
CALCULATE(DISTINCTCOUNT(Sales[Customer]);ALLEXCEPT(Sales;Sales[Sales Rep]))
Now I need to point out which sales representatives's average tickets are above or equal the average and which ones are below it, but I can't get this Average Avg Ticket formula to give the correct results (I expect the average average ticket to be (20,00 + 24,67 + 31,33 + 24,00)/4 = 25,00 for the manager A's team). What am I doing wrong?
Average AvgTicket =
CALCULATE(AVERAGEX(Sales;[Avg Ticket]);ALLEXCEPT(Sales;Sales[Manager]))
Could anybody please help?
Thanks in advance!
Jeff
Solved! Go to Solution.
Instead, add a new table and then a calculated column.
Table2 = SUMMARIZE ( Sales, Sales[Manager], Sales[Sales Rep], "Avg Ticket", DIVIDE ( SUM ( Sales[Sales Amount] ), DISTINCTCOUNT ( Sales[Customer] ), 0 ) )
Average AvgTicket = CALCULATE ( AVERAGE ( Table2[Avg Ticket] ), ALLEXCEPT ( Table2, Table2[Manager] ) )
Instead, add a new table and then a calculated column.
Table2 = SUMMARIZE ( Sales, Sales[Manager], Sales[Sales Rep], "Avg Ticket", DIVIDE ( SUM ( Sales[Sales Amount] ), DISTINCTCOUNT ( Sales[Customer] ), 0 ) )
Average AvgTicket = CALCULATE ( AVERAGE ( Table2[Avg Ticket] ), ALLEXCEPT ( Table2, Table2[Manager] ) )
Could you please help me out i need this for my work tommorrow??
Hello Everyone,
I'm fairly new to DAX measures and i ran into a problem at work. So i have an employee data base table and i want to count total number of employees, all managers in each dept, all supervisors and instructors from a table.
To calculate all the employees i've used following epression and it worked.
Total no. of Employees : COUNTROWS([EmployeesDataBase])
But now i want to calculate total number of managers from (Table - EmployeeDataBase) Column(JobDescription). jobDesc isn't named consistently. They used "Manager 'Somedept'" and also "Mgr". So i wanted to count number of rows from "jobDesc" Column so in excel i could have used wild card " (COUNTIFS ( [jobdesc], {"*Manager*, "*mgr*"})
But Can't figure out how to use this formula in DAX Measure.
PLZ Help. I need this formula for my work Tmrw. Thanks in Advance.
Hi. Use this measure:
CountManagers = COUNTAX ( Table1, SEARCH ( "Mgr", Table1[Jobdesc], 1; BLANK () ) ) + COUNTAX ( Table1, SEARCH ( "Manager", Table1[Jobdesc], 1, BLANK () ) )
Let me know if works in your scenario.
Regards
Victor
Lima - Peru
This works perfectly fine. You are the life saver. Many Thankx. Keep up the good work!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |