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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Using SUM in a Calculated Column returns unexpected results

Hello,

I thought I had a good understanding of Context Evaluation in DAX (I hope I still do) but ran into this scenario

 

As an example I have a very simple table Imported into Power BI Desktop from an SQL Server database.  The table contains the following:

 

 Table1.JPG

Then I create a Calculated Column in PowerBI Desktop as follows:

 

Total_Revenue_Col = sum(Regions[Revenue])

 

I also Add a Slicer to the report in Power BI Desktop that contains the Region as follows:

 

table2.JPG

 

Then I add the Total_Revenue_Col to the table in the report to get:

 

table3.JPG

The problem is no matter what I select on my Slicer, I always get 1600 for Total_Revenue_Col.  Based on my understanding of using SUM in a calculated column is that anything related to the Row Context will be Ignored but the Filter Context will be considered.

 

So what is selected in the Slicer will be used to Filter the main table and then the sum(Regions[Revenue]) will be evaluated on whatever rows are returned from that action.

 

So based on this if I select Asia as the Region I expected to see 600 for Total_Revenue_Col and 1000 for Europe and I should get 1600 if nothing is selected in the Slicer.

 

However this is what I get if for Example I choose Asia:

 

table4.JPG

Why isn't the Total_Revenue_Col value coming back as 600 in this case?

 

Thanks.

1 ACCEPTED SOLUTION

Under visualization pane in the values Click on the "ratio" column and click on the last value "show value as"  and click percent of grand total.

 

This should give you the desired result.

 

Thanks

View solution in original post

7 REPLIES 7
sqlguru448
Helper III
Helper III

Hi @Anonymous

 

Instead of creating Calculated column for Total Revenue, try to create calculated measure.By doing so you can see 600 when you apply slicers/filters.

 

Thanks

Anonymous
Not applicable

Thanks for the quick response.

 

Eventually what I want to do is take the Revenue and Divide it by the Total_Revenue_Col for each row in order to the get the % for the selected Region.

 

In this case the if I create the Measure as the SUM of the Revenue column and drag into the table it will still be equivalent to the Revenue field due to the context created by the surrouding area.

 

I am wondering why the logic with the Calcualted Column using the SUM does not work as expected.  I've seen it documented in several places that it should work, I.e., the value from the Slicer gets applied to the table containing the data and then the Calcualted Column expression is executed on what is returned.

 

If you are trying to Divide, you can create a calculated column  using DAX in powerbi

 

DIVIDE(Region[Revenue]|SUM(Region[Revenue])|0)

 

if you want to see slice and dice for divide column as well then I would suggest you to create a new divide measure which will change based on selection in your slicer.

Anonymous
Not applicable

table6.JPG

 

I add the Calculated Column Ratio = DIVIDE(Regions[Revenue],sum(Regions[Revenue]),0).  The result is above.  It still has the same effect,I.e., dividing by the entire sum rather than just the sum for Asia which should be 600.

 

Thanks.

Under visualization pane in the values Click on the "ratio" column and click on the last value "show value as"  and click percent of grand total.

 

This should give you the desired result.

 

Thanks

Anonymous
Not applicable

Thank you, that does work indeed.  So to summarize, I have two Calculated Columns as follows:

 

Total_Revenue_Col = sum(Regions[Revenue])

Ratio = DIVIDE(Regions[Revenue],Regions[Total_Revenue_Col],0)

 

for 'Ratio' I switch 'Show value as' to 'Percent of grand total' and this is my final result:

 

View1.JPG

 

So here although Total_Revenue_Col is showing 1600, the Ratio is actually dividing 100/600 where 600 is the total of Asia. 

 

So I have also created a new Measure as follows:

 

Total_Revenue_Me = CALCULATE(sum(Regions[Revenue]),all(Regions[Country]))

 

Because CALCULATE triggers context transition (I.e., Row Context becomes Filter Context) I apply the ALL command on the Country so that it does not become part of the Filter Context (otherwise the total will just be equal to the Revenue on that row).

So now this is what I have:

 

View2.JPG

Now I can see the 600 properly showing up.  But keep in mind the Raio is not dividing on Total_Revenue_Me.

 

What I still find perplexing is why I didn't get this same effect for the Total_Revenue_Col?

 

In the book 'The Definitive Guid to DAX' I read that when SUM is used in a Calculated Column, the formula will compute the sum of all values in the current filter context and in the case above the 'current filter context' will be Asia.

Even if there is a row context, SUM ignoes it. Instead, it uses the filter context and the filter context is full set of row. You will get thegrand total o revenue and same value for all rows. check 4-7 in the book you are trying to refer. (Definitive guide to DAX)

 

Try using SUMX since it creates an iterator which will evaluate row by row.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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