calculated measure vs formula?

[jbaich]

Hi all, I am trying to figure out how to use a calculated measure as part of an equation for another calculated measure... Basically I'm trying to create a calculated measure to calculate the Coefficient of Dispersion (COD). Excel 2016 has this formula built in, but I'm trying to create it in my data model so that I can use it in my powerpivot tables. I have a calculated measure for median and I'm trying to use that in my COD measure, which is the Average Absolute Deviation (from the Median) / Median

So I added a calculated column to try to calculate the ABS difference for each row from the median, however when i try to use my Median Calculated measure, it returns Zero values.

My calculated Measure for median is named [MedianRatio]

The formula in the calculated column should be something like:
=ABS(MyTable[Ratio] - Median[Ratio])

I thought that if i used the median formula "median(MyTable[Ratio])" instead of the calculated measure "Median[Ratio]" in the formula, it might not adjust the median based on the pivot table filters...? ie. it would use the table median rather than the filtered sample median...?

So i guess what i'm asking is... am I wrong about how the median formula works in a calculated column? and can you not use calculated measure to derive other calculated measures?

Thanks,
joe

 

[jbaich]

Hi all, haven't received any replies so I thought i'd try uploading a sample workbook that shows the (correct) results i get using excel formulas and the incorrect results i get when trying to create calculated measures... hopefully this helps clarify what I'm trying to figure out and the track I'm currently on.

https://www.dropbox.com/s/0fpwakqp77xpsrl/CODTest.xlsx?dl=0

Thanks,
Joe

 

[Ozeroth]

Hi Joe

You can use this definition of AVEAbsDiff to fix things:

AVEAbsDiff =
VAR Med = [RatioMedian]
RETURN
    AVERAGEX ( CODTestTable, ABS ( CODTestTable[Ratio] - Med ) )

With this definition, you don't need ABS Diff from Median(Yr) or AVE ABS Diff columns.

The above measure calculates the median in the current filter context (stored in variable Med) then iterates over the rows of CODTestTable and calculates the average difference between Ratio & Med.

The problem with having the differences from median pre-computed in columns was that the median used in these calculations didn't respond to the filter context.

Owen

 

[jbaich]

Thanks Owen! Can’t wait to try this out when i’m back in the office on Monday!

 

[jbaich]

Hi Owen, thanks for the help! It took me a little while to figure out that i needed to put a colon before the = sign in ABSAveDiff : =, but i think it's working now!!!

Is that maybe a difference in Syntax between Excel Power Pivot and Power BI? All of the articles I googled on using variables with DAX showed examples like yours that did not have the colon...?

Thanks,
Joe

 

[Ozeroth]

Hi Joe,

Glad it's working

Yes the syntax is slightly different in different situations:

• PowerPivot measure grid:
  MeasureName := [DAX]<code></code><code>
• PowerPivot measure dialogue box:
  = [DAX]<code>
  </code><code>
• Power BI Desktop:
  MeasureName = [DAX]<code></code><code></code></code></code>

<code><code><code>
Apologies for the confusion - I wasn't intending to be specific about that part of the code. I just intended you to take everything after the first equals sign and replace your code after the first equals sign.

Owen</code></code></code>