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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Need help in calculated column - count function

Hello Everyone. So my requirement is to create a column - "Serial Number Count" in the matrix table. I have used the below dax in order to group by all the columns in the table but, the issue is, for few rows the count is coming correctly and for few rows the count is coming too high, which is wrong. For few of the wrong counts, the data is populated as the whole total from the table. I am attaching the screenshots below. Please refer them. 

 

I am using 2 different Dax's : (

1) 

Serial Count = SUMMARIZE(agg_invent_serialid_chatbot,agg_invent_serialid_chatbot[PRODUCT_NAME],agg_invent_serialid_chatbot[INVENT_LOCATION_NAME],agg_invent_serialid_chatbot[Config ID],agg_invent_serialid_chatbot[ITEM_NUMBER],"Serial Count",DISTINCTCOUNT(agg_invent_serialid_chatbot[INVENT_SERIALID]))
 
2) 
Serial Count = SUMMARIZE(agg_invent_serialid_chatbot,agg_invent_serialid_chatbot[ITEM_NUMBER],"Serial Count",DISTINCTCOUNT(agg_invent_serialid_chatbot[INVENT_SERIALID]))
 
Note - I am not sure which DAX is correct so that is why I have mentioned both of them.

 

Screenshot with correct count:

vashu123_1-1694538399312.png

 

Screenshot with wrong count:

vashu123_2-1694538456283.png

The right count of this product is 221 but it is showing as 5232 which is the total of that product in the source.

How do I fix this?

Any help would be much appreciated. Thank you so much in advance!

2 REPLIES 2
MargusMartsepp
New Member

The DAX functions you're using, specifically SUMMARIZE, are generally used to create new tables rather than calculated columns. This could be the reason why you're getting incorrect counts.

Use a Measure Instead of a Calculated Column

Serial Count Measure = 
DISTINCTCOUNT(agg_invent_serialid_chatbot[INVENT_SERIALID])

 If you need a calculated column you could use:

Serial Count Column = 
COUNTROWS(
    FILTER(
        ALL(agg_invent_serialid_chatbot),
        agg_invent_serialid_chatbot[PRODUCT_NAME] = EARLIER(agg_invent_serialid_chatbot[PRODUCT_NAME]) &&
        agg_invent_serialid_chatbot[INVENT_LOCATION_NAME] = EARLIER(agg_invent_serialid_chatbot[INVENT_LOCATION_NAME]) &&
        agg_invent_serialid_chatbot[Config ID] = EARLIER(agg_invent_serialid_chatbot[Config ID]) &&
        agg_invent_serialid_chatbot[ITEM_NUMBER] = EARLIER(agg_invent_serialid_chatbot[ITEM_NUMBER])
    )
)

 

Anonymous
Not applicable

Hi @MargusMartsepp I have tried creating the calculated column using the DAX you've mentioned. I am getting wrong count as below:

vashu123_0-1694540318975.png

The first column is the calculated column which I have created using the dax you have mentioned.

Am i doing any thing wrong here?

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.