cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Maheshb Frequent Visitor
Frequent Visitor

Integer value column is summing up in line chart

Hi,

 

I have table in SQL with 3 columns Date,Table_name,no_of_records.For each date there will be 2 or 3 rows.When i'm trying to show this trend in power Bi i.e. no_of_records in y axis and date in x-axis using line chart the no_of_records is summing up for each date,but my requirement is not to summarize but to show each value of no_of_records on each day.Even I tried with other charts like bar chart,line and stacked column chart,clustered column chart etc but no luck.

 

could someone pls let me know how i can achieve not to summarize but to show each value of no_of_records on each day.

 

 

 

7 REPLIES 7
Moderator v-qiuyu-msft
Moderator

Re: Integer value column is summing up in line chart

Hi @Maheshb,

 

In your scenario, as you put Date column in line chart X-axis and Date has many records on the same day, the line chart will show aggregated values based on X-axis group for each day. 

 

To work around the issue, you can create a index column in Query Editor, then drag this index column to X-axis as well, then drill all levels to see desired values. 

 

e1.PNGe2.PNG

 

Reference: 

https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Maheshb Frequent Visitor
Frequent Visitor

Re: Integer value column is summing up in line chart

Hi Qiuyun,

 

Thanks a lot for your response!! Actually i want to filter with Table_name, date(Audit_Capture_Date in my report) columns and see the trend in my report.Eg: for the date 12/1/2018 like the way u mentioned along with 3 entries for the tables a,b,c there can be one more record for table 'a'  on the same date 12/1/2018.Can I use the index column in this case as well? My report looks something like below.Table Vs count trend.PNG

 

Also, i will use the index column in x-axis  but don't want to display the index values 0,1,2 etc in the line chart.Is there any way to achieve this, pls let me know.

Moderator v-qiuyu-msft
Moderator

Re: Integer value column is summing up in line chart

Hi @Maheshb,

 

As I mentioned previously, the line chart visual is used for display aggregated values instead of raw data. 

 

In your scenario, if you would like to display three data points for the same X-axis value, the workaround is add another column in X-axis (eg: index column) to distinct records within the same date. The index column is not able to hide after putting this column in x-axis. 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Maheshb Frequent Visitor
Frequent Visitor

Re: Integer value column is summing up in line chart

Hi Qiuyun,

 

Thank you for your response.Other than line chart, is there any other chart/visual that can be used where i can achieve my requirement without using index column.

 

Thanks.

Moderator v-qiuyu-msft
Moderator

Re: Integer value column is summing up in line chart

Hi @Maheshb,

 

Based on my research, there is no such OOTB chart visual. You can try to use R visual to plot it with desired results but you need to research for R scripts firstly: https://docs.microsoft.com/en-us/power-bi/desktop-r-visuals

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Maheshb Frequent Visitor
Frequent Visitor

Re: Integer value column is summing up in line chart

Hi Qiuyun,

 

Thank you for your response! I'm using line chart to achieve my requirement but i'm facing a issue where the record count has been increased from 61 to 65 on the 3rd jan 2019, instead of displaying increasing curve in the line chart, it is displaying decreasing curve as attached.Could you please let me know how to rectify this.Also pls let me know how to refresh only the particular visual in the report.

 

line chart error.PNG

 

Thanks  : )

Moderator v-qiuyu-msft
Moderator

Re: Integer value column is summing up in line chart

Hi @Maheshb,

 

The line values depend on used raw data. You can change visual type to table visual, set Don't Summarize for the value which you set Count aggregation in the line chart. Manually calculate the count values to check if they are correct. 

 

We can't refresh specific visual data in Power BI desktop, we can just refresh specific query table. If the query table is only by this one line table visual, you can right click the table to choose Refresh Data: 

 

w2.PNG

 

 

Best Regards,
Qiuyun Yu 

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