cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hoggwildd
Regular Visitor

Calculating a COUNT of a measure

Hello everyone,

 

I am new to this and am self taught using the book Power Pivot and Power BI so forgive me if I dont use the correct lingo.

 

I have written the following measure:

 

CALCULATE([Installed $],Issued[SalesRepName],USERELATIONSHIP(Jobs[Status Date],'Date'[Date]))

 

where [Installed $]: = CALCULATE(Jobs[Sum of GrossAmount],Jobs[JobStatus]="Complete & In house"||Jobs[JobStatus]="Complete & Paid"||Jobs[JobStatus]="Complete & Unpaid"||Jobs[JobStatus]="Complete/Await Insp"||Jobs[JobStatus]="Warranty transfer"||Jobs[JobStatus]="Manufacture War Only"||Jobs[JobStatus]="Legal")

 

This works perfect and the way I want to give me a total $ amount installed by sales rep, I have slicer for area and timeline on and everything works. 

 

THE PROBLEM: I cannot figure out for the life of me how to have a count of the measure. For example if this measure totals $50,000 in sales for one rep over one month, I want to know how many jobs that is. No matter what I have tried, count, counta, countx, countax, etc, nothing seems to work. ANY IDEAS?

 

Thanks for taking the time to read this and help if you can,

 

HW

1 ACCEPTED SOLUTION

If each job is on it's own row of the jobs table, and you're looking to count jobs then I'd just change one thing in your measure to get job count.

 

 = CALCULATE(COUNTROWS(Jobs),Jobs[JobStatus]="Complete & In house"||Jobs[JobStatus]="Complete & Paid"||Jobs[JobStatus]="Complete & Unpaid"||Jobs[JobStatus]="Complete/Await Insp"||Jobs[JobStatus]="Warranty transfer"||Jobs[JobStatus]="Manufacture War Only"||Jobs[JobStatus]="Legal")

 

This should count the jobs, instead of adding up the amount of the jobs.

 

Does that work?

 

View solution in original post

5 REPLIES 5
v-ljerr-msft
Microsoft
Microsoft

@hoggwildd


THE PROBLEM: I cannot figure out for the life of me how to have a count of the measure. For example if this measure totals $50,000 in sales for one rep over one month, I want to know how many jobs that is. No matter what I have tried, count, counta, countx, countax, etc, nothing seems to work. ANY IDEAS?

According to your description, you can use a new measure which is similar to the measure you provided above to get the number of jobs in current context. Please refer to the formula below:

CALCULATE([NumberOfJobs],Issued[SalesRepName],USERELATIONSHIP(Jobs[Status Date],'Date'[Date]))
 
[NumberOfJobs]: = CALCULATE(COUNTROWS(Jobs),Jobs[JobStatus]="Complete & In house"||Jobs[JobStatus]="Complete & Paid"||Jobs[JobStatus]="Complete & Unpaid"||Jobs[JobStatus]="Complete/Await Insp"||Jobs[JobStatus]="Warranty transfer"||Jobs[JobStatus]="Manufacture War Only"||Jobs[JobStatus]="Legal")

Regards

Beckham
Helper II
Helper II

Is each job on it's own row? What does the data look like? How do you know a job is a job?

Thank you for taking the time to try and help.

 

I have 3 main tables in my workbook, ISSUED, LEADS and JOBS. 

 

For the report I am working on I get most of the data from the ISSUED table, but the actual sold business comes from the JOBS table, Each job is on its own row and I sort by the column "job status" on the jobs table for this measure. It only uses jobs that have one of the "status" I called for. It seemed a very complicated and took me almost a month to figure out how to write it and make it work (might have been easy for someone that knows what they are doing! LOL)

 

So each table has many columns and rows, all with customer data, some of the columns are dublicated on each table. I created more tables to be able to tie the main three together (had to remove dublicates to make them work together).

 

I hope that answers your question without confusion and gives you enough to help me.

 

THanks again,

HW

If each job is on it's own row of the jobs table, and you're looking to count jobs then I'd just change one thing in your measure to get job count.

 

 = CALCULATE(COUNTROWS(Jobs),Jobs[JobStatus]="Complete & In house"||Jobs[JobStatus]="Complete & Paid"||Jobs[JobStatus]="Complete & Unpaid"||Jobs[JobStatus]="Complete/Await Insp"||Jobs[JobStatus]="Warranty transfer"||Jobs[JobStatus]="Manufacture War Only"||Jobs[JobStatus]="Legal")

 

This should count the jobs, instead of adding up the amount of the jobs.

 

Does that work?

 

View solution in original post

Beckham,

 

IT WORKED! Not sure how or why to be honest and at this point it does not matter.

 

THANK YOU VERY MUCH!

 

HW

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors