Sum of measure - Table doesn't return desired value

[stizein]

I have a table visualization that is not yielding the desired total for a calculated measure within the visualization.  The individual rows display the correct amount but the total itself does not.

The measure is located on the Actuals data table and is as follows

Total Compensation = [Compensation] + [Total Corp Compensation]  + [Total Comm Practice Comp] + Sum('Admin Comp Allocation'[Admin Compensation])  + [Total Small Comm Practice Comp] + [Total Benefit Practice Comp] + [Total Surety Compensation]

What do I need to do to calculate the sum of the measure to return the desired result?

[stizein]

I have a table with a calculated measure on a data table named Actuals as follows

Total Compensation = [Compensation] + [Total Corp Compensation]  + [Total Comm Practice Comp] + Sum('Admin Comp Allocation'[Admin Compensation])  + [Total Small Comm Practice Comp] + [Total Benefit Practice Comp] + [Total Surety Compensation]

The values on the individual rows are accurate within the table however the total for the column is incorrect.  How do I sum the measure Total Compensation (the values in each of the rows within the table) to obtain the desired result?

[MFelix]

Hi @stizein,

You should add a SUMX aggregator to your measure when you make the measures this are based on context so when you get to the final row on your table the context changes since the values for each line are not the same so the calculation may defer.

Try something like this:

Total Compensation Overal =
IF (
    HASONEFILTER ( TABLE[Column] );
    [Total Compensation];
    SUMX ( Table; [Total Compensation] )
)

Be aware that depeding on the way your measures are setup this may need some changes, if you can provide some sample data I can help you better.

Regards,

MFelix

[stizein]

How do I apply the filters within the formula that you provided or at least for the year filter?  

 

[MFelix]

Hi @stizein,

The HASONEFILTER formula isnt' related with the slicers but with the columns you have in the table.

Columns in a table or a Matrix visual are filters that are applied to the measures. Example when you create a table visual with Names on one column the names are filtering your measure accordingly.

Regards,

MFelix

[stizein]

Okay, then the formula you provided did not yield the correct results and I'm not sure how to provide additional information in order to get the help needed.  The data is confidential and there are formulas within formulas to generate the results for the total compensation.  Let me know how best to proceed.

[stizein]

Here are the additional calcalations on the Total Compensation column that I am wanting to sum values of.  Is there a way to sum the total compensation highlighted at the particular level names within the table? The table is grouped by level name which is essentially a location/department combination a sample table provided below.

Total Compensation =[Compensation] + [Total Corp Compensation]  + [Total Comm Practice Comp] + Sum('Admin Comp Allocation'[Admin Compensation])  + [Total Small Comm Practice Comp] + [Total Benefit Practice Comp] + [Total Surety Compensation]

Compensation =CALCULATE(Sum(Actuals[Value])| FILTER(Actuals| Actuals[CompensationFlag] = "1" && Actuals[TableSource] = "OpForecast" && Actuals[ReportedLevelFlag] = "1"))

Total Corp Compensation =CALCULATE([Corp Compensation]| ALLEXCEPT(Actuals| 'Calendar'[Year]| Actuals[ReportedLevelFlag])) * [% of Compensation]

Total Small Comm Practice Comp =CALCULATE([Small Comm Practice Comp]| ALLEXCEPT(Actuals| 'Calendar'[Year]| Actuals[ReportedLevelFlag])) * [Small Comm Comp %]

Total Benefit Practice Comp =

CALCULATE([Benefit Practice Comp]| ALLEXCEPT(Actuals| 'Calendar'[Year]| Actuals[ReportedLevelFlag])) * [Benefit Comp %]

Total Surety Compensation =

CALCULATE([Surety Practice Comp]| ALLEXCEPT(Actuals| 'Calendar'[Year]| Actuals[ReportedLevelFlag])) * [Surety Comp Percent]

Small Comm Comp % =

[Small Commercial Revenue] / [Total Small Commercial Revenue]

Small Commercial Revenue =CALCULATE([Net Revenue]| FILTER(Actuals| Actuals[TypeFlag] = "2" && Actuals[TableSource] = "OpForecast"))

Total Small Commercial Revenue =CALCULATE([Small Commercial Revenue]| ALLEXCEPT(Actuals| 'Calendar'[Year]| Actuals[ReportedLevelFlag]))

Total Benefit Practice Comp =CALCULATE([Benefit Practice Comp]| ALLEXCEPT(Actuals| 'Calendar'[Year]| Actuals[ReportedLevelFlag])) * [Benefit Comp %]

Benefit Comp % = [Benefit Revenue] / [Total Benefit Revenue]

Benefit Revenue = CALCULATE([Net Revenue]| FILTER(Actuals| Actuals[TypeFlag] = "3" && Actuals[TableSource] = "OpForecast"))

Total Benefit Revenue = CALCULATE([Benefit Revenue]| ALLEXCEPT(Actuals| 'Calendar'[Year]| Actuals[ReportedLevelFlag]))

Benefit Practice Comp = CALCULATE(Sum(Actuals[Value])| FILTER(Actuals| Actuals[TableSource] = "OpForecast" && Actuals[CompensationFlag] = "1" && Actuals[Practice Type Flag] = "2"))

Total Surety Compensation = CALCULATE([Surety Practice Comp]| ALLEXCEPT(Actuals| 'Calendar'[Year]| Actuals[ReportedLevelFlag])) * [Surety Comp Percent]

Surety Practice Comp = CALCULATE(Sum(Actuals[Value])| FILTER(Actuals| Actuals[TableSource] = "OpForecast" && Actuals[CompensationFlag] = "1" && Actuals[Practice Type Flag] = "4"))

Surety Comp Percent = MAXX(DISTINCT(Actuals[Surety Comp %])|MAX(Actuals[Surety Comp %]))

[MFelix]

Hi @stizein,

How did you add the HASONEFILTER to your formula? Can you please send it also?

Is the HeadCount and Revenue/Head measures or columns that you sum up?

regards,

MFelix

[stizein]

Tot Compensation =
IF (
HASONEFILTER ( Actuals[Level Name] )|
[Total Compensation]|
SUMX ( Actuals| [Total Compensation] )
)

This is the formula that I added for the Total Compensation and the amount was way off.  Yes, the Headcount is totaled and Revenue/Head measure is calculated.  The Headcount did not pull the sum of the rows, but rather used another field that would provide the total desired.  Tot HeadCount = if(HASONEFILTER(Actuals[Level Name])|[Total Headcount]|sum(AllocatedEmployees[FTE]))

Revenue/Head uses the Tot Headcount which provides the accurate total and another value 

Net Revenue = CALCULATE(([Commissions and Fees Plus Contingent] + [Misc Revenue]) - [Outside Commission]| Actuals[ReportedLevelFlag] = "1")

[MFelix]

Hi @stizein,

Believe that your issue is related with this part of the Total Compensation

Sum('Admin Comp Allocation'[Admin Compensation]) 

Although it gives you the expected value on the the line level when you add it to others measure the total outcome is different.

Just to check, try to make your Total Compensation without this part of the measure and check if the SUMX calculates correctly.

Then get back to me.

One option is to calculate the value based on a temporary table using a variable, but lets do one step at a time.

Regards,

MFelix

[stizein]

Removing the Admin Compensation piece did not result in the correct total for all excluding Admin Compensation. 

[MFelix]

Hi @stizein,

Is it possible for you to share your file through private message?

It's difficulty to pin point thing without looking at actual data, your measures have several variables and it's not easy to make a mock-up of your data.

Regards,

MFelix

[stizein]

MFelix,

The data will be associated with the file and therefore, I will not be able to send your direction.  I would assume some sort of NDA would need to be signed as well.  Is there an option to connect via screenshare vs sharing the file?