topic Re: Opposite of sum function in DAX Commands and Tips

Opposite of sum function

richard-powerbi — Thu, 18 Jun 2020 17:20:46 GMT

So there's the SUM function as a measure or explicit measure.

A, 10

A, 20

B, 15

Returns this:

A, 30

B, 15

Now let's say a faulty table already summed the values, but the amount of rows is equal to the amount of rows before the sum. Like this:

A, 30

B, 15

How can I create the opposite measure of SUM that divides the value by the count it occurs per group (A/B in this example), resulting in this:

A, 15

B, 15

As you can see the end result is more like a prediction than true to reality, but that's okay.

Thanks!

Re: Opposite of sum function

lbendlin — Thu, 18 Jun 2020 17:27:29 GMT

Don't use DAX to fix a mistake that was made in a previous step. Prevent that mistake in your process.

Re: Opposite of sum function

stevedep — Thu, 18 Jun 2020 18:09:58 GMT

This post should help you get there;

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculated-column-show-subtotal-of-filter-against-every-row/m-p/1144102#M17181

Re: Opposite of sum function

richard-powerbi — Thu, 18 Jun 2020 18:25:12 GMT

lbendlin I wouldn't be asking this if I could fix it in a previous proces...

stevedep thanks but I don't see how this could help me...

Re: Opposite of sum function

mahoneypat — Thu, 18 Jun 2020 22:28:35 GMT

Here is an expressiont that works with your sample data. I called the table 'Reverse'.

Reverse Sum = VAR __thisletter = MIN ( Reverse[Letter] ) VAR __count = CALCULATE ( COUNTROWS ( Reverse ), ALL ( Reverse ), Reverse[Letter] = __thisletter ) VAR __thisaverage = AVERAGE ( Reverse[Number] ) RETURN DIVIDE ( __thisaverage, __count )

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Opposite of sum function

Anonymous — Fri, 19 Jun 2020 07:53:51 GMT

Hi richard-powerbi ,

Count the number of rows for each id and divide it by the total.

Create a Calculated Column

Rows_id =

var a = CALCULATE(COUNT('Table'[Column1]),ALLEXCEPT('Table','Table'[Column1]))

RETURN

DIVIDE('Table'[Column2],a)

Regards,

Harsh Nathani