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
Anonymous
Not applicable

HELP: Count of values filtered by a SUM (measure)

Hi

 

Quick explanation of the data:

 

We are measuring productivity of data capturers using points according to the amount of work that they do.

 

Data is daily.

 

+ve points mean that the target is being reached and -ve means there is a shortfall.

 

 

 

I am trying to display the count of users who have a positive point balance to show the amount of users who are reaching their target.

 

 

 

I do not want to group in the query editor or write a new sql query as this will make the date slicers in the report useless.

We need the report user to be able to filter on months and have the count change to show how many users have made the target in which ever date range the report user selects.

 

 

I have tried writing an if statement measure to say Yes if the sum of points is positiive and no if it is negative. -- The measure works but I cannot put it in a slicer.

 

I have also tried created a measure of Sum(points) and then add that to the filter of the card that is showing the count but when the measure is added as a filter I cannot input numbers into the filter.

 

I even tried a measure: calculate(distinctcount(user),sum(points)>0) ----this doesn't work.

 

Please help! 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Found a work around.

 

The filter works on a matrix and you can use the matrix to display the total.

 

Not ideal but it works.

 

 

View solution in original post

14 REPLIES 14
v-huizhn-msft
Employee
Employee

Hi @Anonymous,

Based on my understanding, you create a slicer including date, it shows how many users have made the target in which ever date range the report user selects. Why do you need to create a measure to display "Yes" or "No", and try to put it in a slicer? Could you please list an sample table, and post your desired result clearly? So that we can post solution which is close to your requirements.

Thanks,
Angelia

Anonymous
Not applicable

Please see a sample of my data below.

 

Each user has a specific amount captured for the day.

 

One of my requirements is to show who has a positive value summed and who has a negative value summed in a time period which can be chosen by a slicer.

 

Power BI does not let me calculate the distinct count filtered on a sum and I cannot put the sum into a filter of a count.

 

Please help

 

 

 

 

obr.PNG

 

 

Hi @Anonymous,

Based on my understanding, you create a slicer including [Date], you select a period in the slicer, there is a measure to calculate the sum value during the period for each Agent. Then you will calculate the distinct count of Agents based on if the summed value positive or negative or not.

Create the measure using the similar formula:

sum=SUM(Table[obr vs basic])


Then create another two measures.

Distinct-count-positive=CALCULATE(DISTINCT(Table),FILTER(Table,Table[sum]>=0))

Distinct-count-negative=CALCULATE(DISTINCT(Table),FILTER(Table,Table[sum]<0))


>>Power BI does not let me calculate the distinct count filtered on a sum and I cannot put the sum into a filter of a count.

I don't understand the sentence, why did not you put the sum into a filter of a count?

Best Regards,
Angelia

Anonymous
Not applicable

This isn't working.

 

I have a distinct count of 271 agents.

 

Using your methos I get a distinct count of 262 >=sum and 269<sum.

 

It seems like it is calculating per line.

For example if an agent has a positive amount and a negative amount then the agent will show up in both filters.

 

I have managed to display this on a graph easily but I cannot get it to to display in a card.

Anonymous
Not applicable

Bump - help

Anonymous
Not applicable

Help please.

 

Still can't figure this out.

 

This displays perfectly in a graph or a table/matrix but I cannot display the counts.

 

The below measure shows the same count when the filter states greater than or less than.

It is looking at every line item and not the sum.

 

COUNT NEGATIVE OBR vs BASIC = CALCULATE(DISTINCTCOUNT(DATA[Team Leader FNumber]),FILTER(DATA,[SUM OBR vs BASIC] > 0))

I'm having the same issue.

Did anyone find a solution yet?

Anonymous
Not applicable

Found a work around.

 

The filter works on a matrix and you can use the matrix to display the total.

 

Not ideal but it works.

 

 

Hi, check this out... It worked for me and seems to be a perfect solution to your request...

https://community.powerbi.com/t5/Desktop/Count-measure-values/m-p/192113/highlight/true#M84602

I am using individual card visual and each has its own filtering so a matrix won't be useful.

Thanks.

Anonymous
Not applicable

You could try lots of individual matrices with individual filtering on each one.

 

Otherwise.....I'm at loss...something like this shouldn't be so difficult

I have the same issue, I am trying to calculate the number of categories that sum up under/over a certain value and provide a distinct count of items in those categories - all in a measure. I'm starting to think that this is not possible!

Anonymous
Not applicable

Thanks!

 

Will give this a try asap and post feedback

Anonymous
Not applicable

The problem of using 'obr vs basic' as a filter on a count is that it looks at every line item so if one person has a positive line and a negative line then the user will show up on the count filtered by positive 'obr vs basic' and on the count filtered by a negative 'obr vs basic'

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.