Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Caitlin_Knox
Advocate III
Advocate III

Default Summarization

I'm having issues with the default summarization control in Power BI Destkop. For some reason, its inactive and I can't change it from 'Do Not Summarize' and I think its why my measures, that have the SUM function are all returning blanks. The measure is simply a sum of a calculated column that parses out Enrollment quantiy using an IF condition. For example:

S2473 - 00201 = IF(AND([Client_Code]="S2473",[ACCT]="00201"),[Quantity_Enrolled],BLANK())

In this column, I can visually see that it has returned values.

But this measure, returns a blank.

Sum of S2473 - 00201 = SUM(tbl_FixedCost[S2473 - 00201])

 

The only thing that I can see doesn't look right, is that inactive button.2016-02-25_9-48-17.jpg

 

1 ACCEPTED SOLUTION

@Caitlin_Knox - Perhaps a data type issue with your calculated column? Generally that would return an error though in a measure, for example trying to SUM a text column. Grasping at straws here.

 

What if you try an equivalent measure without the column:

 

Sum2 of S2473 - 00201 = SUMX(FILTER(tbl_FixedCost,[Client_Code]="S2473" && [ACCT]="00201"),[Quan​tity_Enrolled])

 

Technically probably a better way to do what you are doing since it avoids the extraneous calculated column and all of the logic is contained within the filter. Essentially what this is doing is returning a table filtered down to just the Client_Code and ACCT that you want and then summing Quantity_Enrolled for only those rows. Should be 100% equivalent to your method. Just curious about whether eliminating that calculated column does anything.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

12 REPLIES 12
ifat_c
Helper I
Helper I

Hi 

I'm trying to build a report using the power BI desktop, my dataset it from: power Bi -> power BI datasets

it's a push dataset.

and a lot of options are grey for me using this dataset: modeling, adding columns.

the only thing enabled in modeling is a new measure button. 

therefor i can't change the default summarization to any field (not a measure! a field in the dataset!)

please help

thanks

xacalus
New Member

Ok, i'm new here and i want to know why my "Do not summarize" option does not work?. I need to use the raw values that are in certain columns of my data table.

 

And i can't even change the parameters in default summarization because they are locked. Please help me.

 

pic.jpg

 
Greg_Deckler
Super User
Super User

I could not recreate this but I may have a more simple data model than yours, I had a single table with columns:

 

Value, Quater, Quarter Tnr

 

I created the following custom column

S2473 - 00201 = IF(AND([Quarter]="Q1",[Quarter Tnr]=1),[Value],BLANK())

 

 

And the following measure

 

Sum of S2473 - 00201 = SUM([S2473 - 00201])

 

 

I'm thinking perhaps the context of the measure? Can you post some details about your data model?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

My data model has 2 external lists from sharepoint that are queried from the sql database, and 2 tables of check register data that are inserted from excel spreadsheets.

I have another report I'm working on from the exact same model that I do not have this issue with.

 

The measure in question is based on a calculated column from one of the external lists- Fixed Cost List. Information is inputted from a billing statement which includes enrollment numbers. The column parses out the enrollment quantity for a given client and account- by using IF AND statement. Like I said before, I can visually see that it returns teh values correctly in the column- but when I go to grab it on a report sheet it returns blank. The column and the measure are built on the same table.

 

The issue seems to be isolated to this one table. I have measures on other tables that return values.

@Caitlin_Knox - Perhaps a data type issue with your calculated column? Generally that would return an error though in a measure, for example trying to SUM a text column. Grasping at straws here.

 

What if you try an equivalent measure without the column:

 

Sum2 of S2473 - 00201 = SUMX(FILTER(tbl_FixedCost,[Client_Code]="S2473" && [ACCT]="00201"),[Quan​tity_Enrolled])

 

Technically probably a better way to do what you are doing since it avoids the extraneous calculated column and all of the logic is contained within the filter. Essentially what this is doing is returning a table filtered down to just the Client_Code and ACCT that you want and then summing Quantity_Enrolled for only those rows. Should be 100% equivalent to your method. Just curious about whether eliminating that calculated column does anything.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you for your suggestions.

I got so frustrated yesterday, I started over. And now it works. Unfortunate that I can't pinpoint what went wrong, but am glad its working now.

You're right, that is a much better way to perfom a SUM operation, thanks for the tip.

Greg_Deckler
Super User
Super User

That setting is always grayed out for Measures.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I don't think smoupre's correct answer has been sufficiently recognized or appreciated here, given the replies after it.  The Default Summarization (and Data Category) cannot be set on Measures.  I'm not sure whether this is reasonable or not, but it is true.  It *does* mirror behavior in Analysis Services MD, so I guess it's reasonable. 

 

The various replies to the OP speak of both columns (including calculated ones) and measures.  No idea what's going on if it won't work with the former, but it definitely doesn't work with the latter.

Ok, that makes sense.

 

But then, why is it returning a blank value? Even when I just select the calculated column, it is still blank.

Phil_Seamark
Employee
Employee

Maybe it is only enabled for columns that it thinks are numeric.  If Power BI thinks the column has characters in it, then it disables this as an option?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

But even if I pin the column on a report sheet, where it auto sumns it- it returns blank. AND I have changed that column data type to Whole Number.

 

I have another Model, built exactly like this, off of the same data set- but with different Gropu numbers and ACCT numbers and such- and they work fine. I'm sure its such a simple setting that I'm not picking up on.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.