How to get the ABS value of a grouped (in a table) field?

[dancarr22]

Hello,

We have a set of data (connecting to SQL Server via Direct Query) for which we'd like to SUM a given field and GROUP BY another field.  This is simple and how Power BI works by default.  i.e. Get total sales by state.

But, then we need to take the ABS value of those GROUPED values.  How can this be done in Power BI?

We cannot just take the ABS values and SUM them.  Need to: (1) SUM by GROUP then (2) Take ABS of those grouped/summed values.

Please see example in Excel -- screenshot below.  Want to calculate the result in column H.

One important note: the Category (A,B,C) field could actually be one of several fields -- so would prefer a way to calc this dynamically for any GROUPed field.

Thanks,

Dan

[v-yuta-msft]

Hi dancarr22,

Try DAX like below:

Total =
ABS (
    CALCULATE (
        SUM ( Table1[Amount] ) / COUNTROWS ( Table1 ),
        FILTER ( Table1, Table1[Category] = EARLIER ( Table1[Category] ) )
    )
)

 

PBIX: https://www.dropbox.com/s/6pukmpudoyfsyv8/How%20to%20get%20the%20ABS%20value%20of%20a%20grouped.pbix....

Regards,

Jimmy Tao

[dancarr22]

Hi Jimmy,

Thanks for your help with this -- and for providing an example.

Unfortunately when I try it in my example -- connecting to Excel -- it does not work.  I did not change the formula -- just cut and paste -- and the fields names are the same.  Seems to be an issue with the EARLIER function.

Error is: "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

Also, is there a way to modify the formula so we do not have to specify 'category' field?  There will be numerous fields we may group/filter by and would like this to be dynamic -- and not have to write a separate formula for every variation.

Thanks again for your help -- greatly appreciated.

Dan

[v-yuta-msft]

Hi dancarr22,

Are you trying to create a measure? I'm afraid a measure can't quite meet your requirement, so you should create a calculate column instead. Also, if there're multiple category columns in your table, you can use ALLEXCEPT(Table, Table[Category1], Table[Category2], Table[Category3], ...) instead of EARLIER() like below:

Total =
ABS (
    CALCULATE (
        SUM ( Table1[Amount] ) / COUNTROWS ( Table1 ),
        ALLEXCEPT ( Table1, Table1[Category] )
    )
)

Regards,

Jimmy Tao

[dancarr22]

Thanks Jimmy -- getting closer to the solution!

A few issues:

(1) As mentioned above - we are using SQL Server with DirectQuery -- Excel was just to show a simple example.  This formula does not work with Direct Query.  Getting error: Function 'SUM' is not allowed as part of calculated column DAX expressions on DirectQuery models.

(2) The ALLEXCEPT references field Category.  What if we grouped by field Currency or Country instead?  Would we need separate formulas each time we group by a different field?  Would prefer this to be more dynamic and work for whatever grouped fields we use.

This worked in Tableau using TABLE_ and/or WINDOW_ functions.  Is there anything like this in DAX?

Thanks again for all of your help.  Greatly appreciated.

Dan

[dancarr22]

A few more attempts -- still not working.

To summarize -- need to:

(1) SUM amounts GROUPed by a given field

(2) Take the ABS of (1)

(3) Show total of (2).

ABS2 = IF(HASONEFILTER(Table2[Category]),ABS(SUM(Table2[Amount])),SUMX(Table2,ABS(SUM(Table2[Amount]))) )


ABS3 = ABS(SUM(Table2[Amount]))


ABS4 = SUMX(Table2,(ABS(SUM(Table2[Amount]))))

[Anonymous]

https://msdn.microsoft.com/en-us/query-bi/dax/abs-function-dax

[dancarr22]

Thanks bccolema -- but this does not answer our quesiton.

I am aware that we can use the ABS value in DAX.  But, how can we take the ABS value of aggregated/grouped data?

If you look at the screenshot of our example you'll see just using ABS does not return the value we want. 

Thanks,

Dan

[Anonymous]

You would need a measure, so you would likely have to do it thru PowerPivot instead of just thru Excel 

abs(sum('Table 1'[Amount])) 

[dancarr22]

Thanks for your help in responding to the question -- can you please show how to do that in Power BI instead of Power Pivot?

Also, note the correct answer should be 13.  This is the absolute value of the GROUPED sum.  Can only take the ABS value of the results AFTER they have been totalled and grouped -- in this case by the category.  But, could be grouped by a variety of other fields.

We are using Direct Query to a SQL Server database to access the data.