Default Summarization Not Working

[Anonymous]

Hi guys,

I'm having a problem using default summarization. My model has 3 tables, d calendar, d fb ads and fb ads.

I've established the following relationship between then:

I've setted default summarization for 'f fb_ads' [spend] as SUM as well as for 'f fb_ads' [impressions].  The columns 'f fb_ads[spend] is setted as fixed decimal number, and 'f fb_ads' [impressions] is an integer.

When try to create a table visual, using the columns: 'd calendar'[date] and 'f fb_ads' [spend], the  default summarization does not work.

And, if I would like to see the sum of spend for a specific day, I need to create a simple measure, like : 

Spend = SUM ( 'f fb_ads'[spend] )

And it works fine:

I've already check if all date's columns are settled as date type. Anyone know what might be the problem ?

• d calendar:

• f fb_ads

[Anonymous]

I have the same issue, 

If you have a closer look at the fields list the Summarization indicator ( sigma ) INT values is no longer visible

i am using the latest version on PBI 2.93.981.0 64-bit (May 2021)

[charleshale]

@Anonymous This is a known bug we have encountered several times where values fail to get recognized as values even though they are shown as such.  The only solution I know of is to do an additional column in DAX like this:   Go To the Data area / Table Tools (on menu bar) / Add Column.   Add column of VALUE ([column name that isnt summing]).  That column will then auto sum.   

[charleshale]

I am having the same issue with default summarization on the latest May 2021 build.   Redoing the file from scratch fixes the issue.  Did the latest build have  a change in a global setting that interferes with default summarization?   When a column has Summarization: Sum chosen on Data/Column tools, I would expect to see a Greek Sigma sign next to the field in the field summary, and have it be able to be used as a sum in visuals instead of a row or column header   

[amitchandak]

@Anonymous , right on the field in the visualization tab.  And check what is summarization.

1. If default summarization is changed after its was dragged into a visual, that visual will not use default summarization .

2. Also if summarization  is changed on visual it will not pick up deafult summarization

3. remove the column and add again and check

[Anonymous]

@amitchandak Thank for your reply.

Unfortunately the problem is still occorring. I was not able to find the Summarization option inside the Visualizations Menu. I remember that in previous versions of PBI Destktop it used to have this option, but, as  I'm using the last version of PBI Desktop, I didn't find the option. 

I've checked the 'f fb_ads'[spend] data type using Fields Menu, and it's setted to SUM.

 

[v-yiruan-msft]

Hi @Anonymous ,

I can't reproduce your problem. What's the version of your Power BI Desktop? In addition to not being able to use the default summarization function, the menu options in your screenshot are also different from mine: it does not contain any aggregation function options. It is strange... Could you please try to create another visual used another numeric field and check whether the default summarization function can work or not? 

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

Hi @v-yiruan-msft ,

First of all, thank for your reply. 

I'm using the 2.87.762.0 64-bit (November 2020) PBI version.

In this report, I've a very similiar table, called 'f g_ads'. Basically, I've the same structure for 'f fb_ads' and for 'f g_ads'.  

In order to provide as much information as possible, I'll include in this reply some screenshots from both of this table, as well as the relasioship of both of than with the 'd calendar'.

• 'f fb_ads'  Table Structure

• 'f g_ads' Table Structure

•  Relationship of Tables

I tried to use the default summary for other numeric fields, but even if these fields are set up as integers or decimals, it appears that PBI is treating it as strings, because everyone is exhibiting the same behavior.

All my date come from MYSQL database, and for some tables, when importing, I need to use some SQL JOINs to import only the data that it matters. 

i.e. 

SELECT d.* FROM `g_ads_anuncio_dias` d #(lf)INNER JOIN g_ads_anuncios a ON d.anuncio_id = a.id#(lf)INNER JOIN g_ads_grupos g ON a.grupo_id = g.id#(lf)INNER JOIN g_ads_campanhas c ON g.campanha_id = c.id#(lf)WHERE c.conta_id = 3"

And, by doing this, the navigation step causes an error, and in orther to keep importing data, I remove this step from the APPLIED STEPS section on Power Query Editor.

But even the tables that don't need to be JOIN are presenting the same problem

. 

I do not know what else could be causing this issue.

Best Regards,

[v-yiruan-msft]

Hi @Anonymous ,

Sorry I can't reproduce your problem... Could you please try to upgrade the version of your Power BI Desktop to the latest one? Besides that, please try to create a new report file and make a table with below data. Then create a table visual  using value field and check whether you can get the aggregation options normally...

ID	Value
id1	1
id2	2
id3	4
id4	3

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

Hi @v-yiruan-msft , how are you doing ?

Over the weekend I've made some test in order to understand better the problem.

First I created the table that you suggested inside the current PBI File that I was working on. The problem still happening, and the Value was not summarized. I think that PBI was interpreting Values column as a String. 

Then I've created a new PBI File and insert your suggestion table in it. The problem did not reappear and everything returned to normal.

After validating the issue didn't occours again, I've imported all my tables from my MYSQL database, and the default summarization started to work again. So I'm creating all my model from scratch, and fortunately, the problem is not occuring. 

In all the tests I was working with the same PBI Version: 2.87.762.0 64-bit (novembro de 2020).

Thank for your time and help. Should I mark your last comment as a solution ?

Best Regards,

[andy_scott42]

I've had this exact same problem, all the tables in my data model were like this. 

However, if I create a brand new PBIX file everything is fine.

Is the only solution to recreate the entire PBIX file? It looks like the file has corrupted somehow. Ideally I don't want to have to recreate my PBIX file.

Is there a global setting that affects the global summarizations? 

Thanks

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Yes, you can mark your post as Answered if the problem has been resolved. 

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.