cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
zamboni1199 Helper I
Helper I

Need new calc column counting all rows with same value in column but not for entire table

I need to calulate all rows with the same value in a column.  I see someone posted this answer:

                     new_calculated = COUNTROWS(FILTER('current table',
                           (EARLIER('current table'[3rd_column]) = 'current table'[3rd_column])))

 

That's great and it works but it's adding up the rows for the ENTIRE table, which I don't want.  My report is filtered to just have part of the table shown (I only selected rows where a certain column = a certain value).  I just want the new calculated column to calculate for the rows shown, not for the entire table.

 

Hope that makes sense, and any help is appreciated! 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
v-piga-msft Resident Rockstar
Resident Rockstar

Re: Need new calc column counting all rows with same value in column but not for entire table

Hi @zamboni1199,

 

It seems that you may filter the report with the date slicer.

 

If it is your case, you could create the calculated column with the measure below.

 

Column =
COUNTROWS (
    FILTER (
        'Table1',
        EARLIER ( 'Table1'[Employee] ) = 'Table1'[Employee]
            && 'Table1'[Date].[Month] = EARLIER ( Table1[Date].[Month] )
    )
)

Here is the result output.

 

result.PNG

 

If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Super User I
Super User I

Re: Need new calc column counting all rows with same value in column but not for entire table

@zamboni1199 Any sample data and expected output will be really helpful to provide the solution quicker and accurate....



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





zamboni1199 Helper I
Helper I

Re: Need new calc column counting all rows with same value in column but not for entire table

Yes, thank you, here you go:

 

Here is the filtered data in my report:

 

Created On        Employee     Number of Occurrences

1/1/2018            John Doe          43

1/10/18              Jill Smith           17

1/21/18              John Doe          43

 

The number of occurrences should read 2 for John Doe and 1 for Jill Smith because I'm only showing data that was Created in January of 2018.  But the Number of Occurrences is showing the total times John Doe and Jill Smith are listed in the table (from January 2018 through the current date).  I just need the Number of Occurrences to match what's showing on my filtered report.  Thanks.

v-piga-msft Resident Rockstar
Resident Rockstar

Re: Need new calc column counting all rows with same value in column but not for entire table

Hi @zamboni1199,

 

It seems that you may filter the report with the date slicer.

 

If it is your case, you could create the calculated column with the measure below.

 

Column =
COUNTROWS (
    FILTER (
        'Table1',
        EARLIER ( 'Table1'[Employee] ) = 'Table1'[Employee]
            && 'Table1'[Date].[Month] = EARLIER ( Table1[Date].[Month] )
    )
)

Here is the result output.

 

result.PNG

 

If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

zamboni1199 Helper I
Helper I

Re: Need new calc column counting all rows with same value in column but not for entire table

Thank you, that was great!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors