Division of calculated columns not giving correct result

[baronraghu]

Hi,

I am trying to perform following simple arithematic function but getting incorrect answer in powerbi. 

I want to divide the entries in Column highlighted in Blue with the maximum Value in that column. The last column highlighted in red is the result.

I have used this function

MaxofA=Max(table[columnhighlughtedin blue])

SumofColumnA=sum(table[columnhighlughtedin blue]))

Divide=( SumofColumnA,MaxofA)

@Ashish_Mathur 

[parry2k]

@baronraghu In case you want the max of column1 and column 2 then ALLEXCEPT is the way to go.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[ebeery]

@baronraghu I presume you are writing this DAX in a measure, not a calculated column?

Keep in mind that when visualizing in a matrix, the filter context is applied to the measure in each row of the matrix.  So in the first row, what is actually being calculated is 10,55,596/10,55,596 = 1.00000

In order to correctly calculate the max for all entries, you need to use a CALCULATE statement to remove the filter context on the current row of the matrix.

Something like:

CorrectedMeasure =
VAR _MaxofA =
    CALCULATE ( MAX ( table[columnhighlightedinblue] ), ALL ( table ) )
VAR _Sum =
    SUM ( table[columnhighlightedinblue] )
VAR _Result =
    DIVIDE ( _Sum, _MaxofA )
RETURN
    _Result

[baronraghu]

Thanks @ebeery and @parry2k 

Table[columnhighlughtedin blue] is a calculated coulmn and the other columns are also filtered. Hence the MAx function was not working correctly.

I realized through some google research that All may not  be helpful rather have to use ALLExcept.

I was able to solve this by using the following method:-

MaxofA=Calculate(Max(table[columnhighlughtedin blue]),AllEXCEPT(Table,filteredcolumn 1, filtered_column 2))

This worked well for me. BUt would like to know if there could be a better way of doing

[parry2k]

@baronraghu you need to add measures:

Measure MaxofA=CALCULATE(Max(table[columnhighlughtedin blue]), ALL ( ) )

Measure SumofColumnA=sum(table[columnhighlughtedin blue]))

Measure Divide=( SumofColumnA,MaxofA)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡