Opposite of sum function

[richard-powerbi]

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

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

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!

[harshnathani]

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

[mahoneypat]

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

[stevedep]

This post should help you get there;

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculated-column-show-subtotal-of-filter-aga...

[lbendlin]

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

[richard-powerbi]

@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...