Forum Discussion
Pivot Table - Subtotals - Custom Calculations
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>
jcarrier,
Directly create the following measure.
ADJ = ABS(SUM('Table '[Balance]))
Regards,
LydiaHi 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
12 Replies
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,
LydiaHi 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
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