Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jcolares
Frequent Visitor

Average of calculation results

Hello, fellows.

 

 

I have this table and these formulas below:

 

forum_post.JPG

 

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

 

 

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@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] )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-chuncz-msft
Community Support
Community Support

@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] )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Thank you very much!

jaygill
Frequent Visitor

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
Community Champion
Community Champion

@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




Lima - Peru

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.