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
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
Employee
Employee

@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
Advocate II
Advocate 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?

 

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
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.