Why Power BIdoes agregate fields by itself

[eliassal]

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 

[v-easonf-msft · Accepted solution]

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.

[eliassal]

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

[eliassal]

Also, as you can notice, I have DatabaseID, it is also sumed which really is disurbing me as well as the IDs column

[eliassal]

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

[themistoklis]

@eliassal 

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:

[eliassal]

OK, I did, the SUM sign disappeared but still on the graph data is not correct as in "Column Values " iis "Count of CurrentSize

[eliassal]

Why for the sake of God Power BI forces us to use an agregate instead of the values of the fields, I don't really understand why so simple scenarion gets so complicated to acheive?

[themistoklis]

@eliassal 

That's because 'Column Values' section in charts only accepts numeric data not categorical data.

So it automatically converts the categorical info to numeric .. by applying the count function

Also I cannot imagine how a graph can display categorical data in values area

[eliassal]

Wich is the case if you look at the snapshot

[eliassal]

No they are numeric, what do you mena by categorical data

[eliassal]

Let me ask the question differently, you have seen the structure of the table, how can I plot a chart to show me size of each database by datein line chart or stacked, then filter by DB name? Can you share the steps to do this please?

[themistoklis]

Sorry i didnt explain it correctly.

Certain visuals require aggregated data in their 'values' section.

An example can be the graphs. You can have categorical data in X axis and a range of values in Y axis but in the values area you must have aggregated data. That's why the graph visual automatically applies an aggregation function. I hope it makes sense

[eliassal]

No not really, so how do I acheive what I want to do, is there any other visual that allows acheiving what I am looking for which is a very basic scenario if I am not mistaken?

[eliassal]

Hi, any idea?