Forum Discussion

jcarrier's avatar
jcarrier
Helper 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
Helper 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        jcarrier,

        Directly create the following measure.

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


        Regards,
        Lydia