cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hoggwildd Frequent Visitor
Frequent 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

Accepted Solutions
Beckham Regular Visitor
Regular Visitor

Re: Calculating a COUNT of a measure

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?

 

5 REPLIES 5
Beckham Regular Visitor
Regular Visitor

Re: Calculating a COUNT of a measure

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

hoggwildd Frequent Visitor
Frequent Visitor

Re: Calculating a COUNT of a measure

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

v-ljerr-msft Super Contributor
Super Contributor

Re: Calculating a COUNT of a measure

@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 Regular Visitor
Regular Visitor

Re: Calculating a COUNT of a measure

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?

 

hoggwildd Frequent Visitor
Frequent Visitor

Re: Calculating a COUNT of a measure

Beckham,

 

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

 

THANK YOU VERY MUCH!

 

HW