Using SUM in a Calculated Column returns unexpected results

[Anonymous]

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:

 

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:

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

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:

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

Thanks.

[sqlguru448]

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]

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.

[sqlguru448]

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]

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.

[sqlguru448 · 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

[Anonymous]

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:

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:

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.

[sqlguru448]

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.