Average of calculation results

[jcolares]

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

[v-chuncz-msft · Accepted solution]

@jcolares,

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] )
)

[jcolares]

Great idea, v-chuncz-msft! It worked well!

Thank you very much!

[jaygill]

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. 

[Vvelarde]

@jaygill

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

[jaygill]

This works perfectly fine. You are the life saver. Many Thankx. Keep up the good work!