Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
baronraghu
Helper III
Helper III

Division of calculated columns not giving correct result

Hi,

 

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

 

baronraghu_1-1638715496688.png

 

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 

4 REPLIES 4
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

ebeery
Solution Sage
Solution Sage

@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



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
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.