Forum Discussion
Data summarizing on charts
I am trying to make a simple chart with just the time on the x axis and the counts of hires in each month. When I drag the hire dates to the y axis or values field, it shows as a total count for each time period; ie a flat bar if it’s a line chart or a bunch of same-sized columns if it’s a column chart. The data is not summarized in the data view, and I can easily see from filtering by these dates that there should be varying values. Both my calendar and the hire date are categorized as time/date. Can anyone advise me on what I’m doing wrong, and even better how to fix it?
6 Replies
Hi Shambles
If both date fields (from your calendar and from the hires table) are of the same data type, and the relationship between them is properly set as one-to-many — with the calendar table on the “one” side — then aggregations like monthly counts should work correctly, as shown in the chart I’ve attached.
Before building the visual, a good way to debug is to create a simple table visual that includes the date fields from both tables. This lets you check if the filtering behaves as expected and helps identify mismatches in data types or relationships early on.
I’m attaching a sample .pbix file I built with dummy employee data to demonstrate the expected behavior.
If your result still looks different, feel free to share a link to your file so we can take a closer look .
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
HI Shambles ,
It sounds like the issue is likely due to how Power BI is aggregating your data or recognizing relationships between your date fields. When you drag the hire date into the values field, Power BI cannot count it directly unless it's explicitly told to. Instead, you should drag the hire date to the X-axis (to represent time) and drag a measure or a field (like an ID or name) into the values area and set the aggregation to "Count" or "Count Distinct".
aThis way, Power BI will count the number of hires for each time period. Also, make sure that your hire date field is properly related to your calendar table, with a one-to-many relationship from the calendar to the fact table. If you're using a calendar table for the X-axis, ensure the relationship is active and the hire date is being filtered through it.
Finally, confirm that the axis is using the correct granularity (month, not day or year) and that Power BI is not auto-summarizing all dates into one bucket due to improper grouping or missing relationships. Once everything is correctly connected and the aggregation set to "Count," your chart should reflect the varying monthly hire numbers.
Hi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
Hi Shambles,
Thanks Ashish_Mathur, rohit1991 and Ritaf1983 for Addressing the issue.
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay PabbuHi Shambles,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Regards,
Vinay PabbuHi @Shambles,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay Pabbu
