Forum Discussion

richard-powerbi's avatar
richard-powerbi
Icon for Post Patron rankPost Patron
6 years ago

Opposite of sum function

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!

Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

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

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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