Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
shrungahv
New Member

Formula to create a new column to get the count of a row with same value/data for a particular month

Hi, Need your help. I am trying to achieve to create a new cloumn in Power BI with formula to get the count of same asset received for a particular month (expected result Column highlighted in Yellow) 

Below is the formula am applying but not returning the correct count. Please can someone help.

 

Asset count = var ast = [Asset] var mnt = [Month] return countrows(filter(filter(Query1, [Asset] = ast), [Month] = mnt))

Query1 is the table name
 

sample.JPG

2 REPLIES 2
ITGeek555
Helper II
Helper II

You could just create a measure

Asset Count = COUNTROWS(TableName)

and then put Asset, Month and Asset Count fields in a table visual

@ITGeek555Thanks for the response. I used the below Measure and it is giving the correct count but subtotal is not displaying correct.

AssetCountGreaterthan = IF(Query1[AssetCount] >= 3, Query1[AssetCount], BLANK())

i have 4 categories under the Matrix Row (Client Name, Asset Name, country, service line) when drill down to Asset Name it is showing 2 asset (asset1 and asset2 with 5 and 4 count respectively however under Client Name subtotal display 28 which should actually show to Subtotal as 9.
 
 
 
 
 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.