Pivot Table - Subtotals - Custom Calculations

[jcarrier]

I'm having a challenge with pivot table subtotal custom calculations.   For example, given a Profit and Loss, all values would show as positive and it would flow as shown below.  This is for a Data Model so I thought of coverting to CUBE formulas and tweaking accordingly but perhaps someone has a much better solution.  Please let me know.

Thanks!

Revenue Items

Subtotal <Revenue - addition>

COGS

Subtotal <Revenue - COGS>

Expense categories

Subtotal <Expense categories - addition>

Other Expense

Subtotal <Other Expense - addition>

Grand Total <Revenue - COGS - Expense categories - Other Expense>

[Anonymous · Accepted solution]

@jcarrier,

Directly create the following measure.

ADJ = ABS(SUM('Table '[Balance]))


Regards,
Lydia

[Anonymous]

@jcarrier,

Based on your description, it is difficult for us to provide you appropriate formulas. Could you please share sample data of your original table and post expected result here?

Regards,
Lydia

[jcarrier]

Hi Lydia -

Thanks for your reply.

Here is a screenshot to better illustrate the subtotals as calculated by Balance (utilizing positive and negative values) and Adj Balance (utilizing all positive values).

As you can see, custom subtotoals are needed for the Adj Balance for (Revenue - COGS) and (Revenue - COGS - Expenses) to match the results of the Balance measure as highlighted below.  Please advise on a solution.  Thanks!

jcarrier

[Anonymous]

@jcarrier,

Do you want to calculate the subtotals which is highlighted with yellow color? If so, could you please post the raw data of your original table which can be copied to Excel? I will test it in my environment.

Regards,
Lydia

[jcarrier]

Hy Lydia -

As requested, I've attached a link to the raw data file to this reply.  I've included the subtotal calculations for both the yellow highlighted and blue highlighted rows.  Essentially, the subtotal for Adj Balance (all positive values) needs to match the subtotals for the Balance column.  The only two subtotals that need this customization are the two blue highlighted rows for Revenue - COGS (Gross Margin) and Grand Total (Net Income).  Hope that helps.  Thanks again!

https://1drv.ms/x/s!AqJ9DWk6pUTxgZVmmmpF7CvU_BmU3w

jcarrier

[Anonymous]

@jcarrier,

Create the following measure in your table.

Measure 2 = ABS(SUM('Table'[Balance]))

ADJ = IF(COUNTROWS(VALUES('Table'[REPORT_CATEGORY]))=1, [Measure 2],SUMX(VALUES('Table'[REPORT_CATEGORY]),[Measure 2]))



Regards,
Lydia

[jcarrier]

Lydia -

I tried the provided measure but it does not give the intended result.  The ADJ column for the Profit and Loss must match the Balance column with all of the values for the column showing as positive.  The measure provided shows the correct summation for the Expense categories but does not properly calculate subtotals for Revenue - COGS or Revenue - COGS - Expenses.  Please advise on a solution.  Thanks again.

jcarrier

[Anonymous]

@jcarrier,

I compare the subtotals in my screenshot and your screenshot, they are same. You can check details in this PBIX file.

If you have questions, please post a screenshot and highlight the incorrect values.

Regards,
Lydia

[jcarrier]

Lydia -

My apologies if I have not been more clear with this request.  First, all values need to show as positive (no negative values) as shown in the ADJ column.  Second, for the ADJ column, the Revenue - COGS subtotal needs to equal 37,501,202 (not 233,312,002).  Third, for the ADJ column, the Revenue - COGS - Expenses subtotal needs to equal 10,591,033 (not 260,222,171).  Please advise on a solution.  Thanks again.

jcarrier

[Anonymous · Accepted solution]

@jcarrier,

Directly create the following measure.

ADJ = ABS(SUM('Table '[Balance]))


Regards,
Lydia

[jcarrier]

Wow, I have to be honest, I have no idea why that formula works!  Can you explain why the subtotals work one way for Revenue - COGS and Revenue - COGS - Expenses and a different way for the other subtotal calculations?  This is amazing indeed.  Thanks for your help!

[Anonymous]

@jcarrier,

Please check how ABS() function works in this link.

In addition, please mark my reply as solution so that other community members would easliy find the solution when they get same issues.

Regards,
Lydia

[jcarrier · Accepted solution]

Hi Lydia -

Happy to mark as solved, my point was that ABS works completely differently in Excel than DAX so the outcome is quite unexpected.  If you use absolute value in Excel the subtotals would be impacted by the absolute value formula itself.  Yet, in DAX measures, ABS seems to allow expression of +/- values to all display as positive yet the subtotals themselves obey the original sign values.  That is quite unexpected indeed, and it's extremely powerful too.  Thanks again.

jcarrier