Forum Discussion
Why Power BIdoes agregate fields by itself
I have a sql table populated by a job everynight to monitore data and log file growth, here are some line of the table
DatabaseID DatabaseName FileID FileName FileType CurrentSizeMB SpaceUsedMB PercentUsed FreeSpaceMB PercentFree AutoGrowth CaptureDate
95 Admin 1 Admin ROWS 36.00 86.56 63.65 49.44 36.35 By 64 MB - Unrestricted 2021-04-08 13:04:05.983
95 Admin 2 Admin_log LOG 200.00 137.82 68.91 62.18 31.09 By 64 MB - Restricted to 2048 GB 2021-04-08 13:04:05.983
95 Admin 1 Admin ROWS 136.00 86.56 63.65 49.44 36.35 By 64 MB - Unrestricted 2021-04-08 13:04:54.860
95 Admin 2 Admin_log LOG 200.00 137.82 68.91 62.18 31.09 By 64 MB - Restricted to 2048 GB 2021-04-08 13:04:54.860
7 AdventureWorks2012 1 AdventureWorks2012_Data ROWS 205.00 191.31 93.32 13.69 6.68 By 16 MB - Unrestricted 2021-04-08 13:04:54.890
7 AdventureWorks2012 2 AdventureWorks2012_Log LOG 160.81 157.57 97.99 3.24 2.02 By 10% - Unrestricted 2021-04-08 13:04:54.890
94 AdventureWorks2016CTP3 1 AdventureWorks2016CTP3 ROWS 100.00 3.06 3.06 96.94 96.94 By 10% - Unrestricted 2021-04-08 13:04:54.923
First of all, when I add the table to Power BI, it agregates as SUM for all decimal fields (CurrentSizeMB SpaceUsedMB PercentUsed FreeSpaceMB....) when I click on the field Values, I see "SUM, Average, Min, Max......) but I don't need anyone of them I just need the value for each DB for each date to be plotted on the graph. HOw it is possible
Hi, eliassal
In order to avoid the aggregation of related data, you need an index column.
Please create a new custom column based on clolumn "DatabaseName" and column "FileName".
ID = 'Table'[DatabaseName]&"_"&'Table'[FileName]Then apply it to the legend of Line Chart to replace your original field "DatabaseName".
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
14 Replies
Also, as you can notice, I have DatabaseID, it is also sumed which really is disurbing me as well as the IDs column
You will notice next to the numeric field the letter 'Σ'
This means that when it will be summed automatically. To prevent this from happening, change the summarization from 'SUM' to 'Don't Summarize'. Image below:
OK, I did, the SUM sign disappeared but still on the graph data is not correct as in "Column Values " iis "Count of CurrentSize
I tried line and stacked column visual, it is also giving some bad graph. it also shoes the sum of all DB sizes for every date where I need to see columns by date by Database, here is a snapshot of the graph showing only SUM of all DBs sizes by date which is not really what I need to see. This is driving me crazy as to make a simple chart
Hi, eliassal
In order to avoid the aggregation of related data, you need an index column.
Please create a new custom column based on clolumn "DatabaseName" and column "FileName".
ID = 'Table'[DatabaseName]&"_"&'Table'[FileName]Then apply it to the legend of Line Chart to replace your original field "DatabaseName".
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.So many thanks, I did. But I am getting something very strange. I have a query (view from sql) which returns all values, especially the field I am interested in which Is [CurrentSize]. I don't see this field in the fields list in spite of the fact that I can see it in the transform Tab table
whereas it is not available in the fileds list
In spite of the fact also, in another visual on the same page I did earlier, I can see the field in the values list but not in the fields list
