Forum Discussion

jcarrier's avatar
jcarrier
Icon for Helper I rankHelper I
9 years ago
Solved

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 M...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    jcarrier,

    Directly create the following measure.

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


    Regards,
    Lydia

  • jcarrier's avatar
    jcarrier
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
jcarrier
Icon for Helper I rankHelper I
9 years ago

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