New Calculated Column dividing column A by max of column B from 2 groups.

[nmyre]

Hello,

I am trying to make a new column that finds the percent between column A and the MAX of column B all within groups. What essentially is happening is that I am taking each location, called "OBAN", and dividing their total sales for each month by the MAX of their total sales goal, titled FYTD Avail Total Auth". All within the fiscal year. There are two tables. Table A has the amounts to be divided and a "period" column that has a relationship with my date table. Here is table A.

The dark blue lines divide the groups. They are based off the first column titled "OBAN". The second column, highlighted in light blue, has the relationship with the date table. The green highlighted column, titled “FYTD GrOB” has the number that need to be divided by the 6th columns MAX, highlighted in red.

Next is the date table. The light blue shows the relationship with the first table. The red "FY" Column is the other group. See Below:

So For each Fiscal Year, "FY" I am looking for the percentage of sales to the maximum goal at the end of they Fiscal Year for each Location. The desired output column is in yellow below:

End goal is to make a timeline graph of % Sales.

Thank you!

[Fowmy · Accepted solution]

@nmyre 

Add this code as a new column to your table 1, Rename Table1 to your correct table name.

% Diff = 

DIVIDE(
    Table1[FYTD GrOb],
    CALCULATE(
        MAX(Table1[FYTD Avail Total Auth]),
        ALLEXCEPT(Table1, Table1[OBAN])
    )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[nmyre]

@Fowmy 

This is very close. Thank you. However, the data set has multiple years so the result is the FYTD GrOb differenece of MAX of all years. I added a Fiscal Year (FY) column, so that should make it easier. It looks like this:

I tried

% Diff = 

DIVIDE(
    Table1[FYTD GrOb],
    CALCULATE(
        MAX(Table1[FYTD Avail Total Auth]),
        ALLEXCEPT(Table1, Table1[OBAN]),
        ALLEXCEPT(Table1, Table1[FY])
    )
)

But it is off. Thoughts?

[v-easonf-msft]

Hi , @nmyre 

Not  very clear.

Maybe you just need the formual as below:

% Diff =
DIVIDE (
    Table1[FYTD GrOb],
    CALCULATE (
        MAX ( Table1[FYTD Avail Total Auth] ),
        ALLEXCEPT ( Table1, Table1[OBAN], Table1[FY] )
    )
)

Best Regards,
Community Support Team _ Eason