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
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
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
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,
LydiaLydia -
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