cancel
Showing results for
Did you mean:
Highlighted
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

Accepted Solutions
Highlighted
Helper II

## 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
Highlighted
Helper II

## 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?

Highlighted
Regular 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

Highlighted
Microsoft

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

Highlighted
Helper II

## 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?

Highlighted
Regular 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

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors