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