Obtain MAX of column and display for each row

0
votes

I'm trying to obtain the MAX of a particular column in a Power BI Report and place this as a new Measure within each ROW of the same dataset. Please see the example below.

Excel Sample

Is this possible in DAX and via DirectQuery/LiveConnection? The report is pointing to a tabular model but due to outside factors the measure must be created in the report.

Thanks

1
powerbidax
What have you researched / tried so far? – Olly
Since you are using an aggregated value have you thought of using a measure instead of a calculated column? – Mitchell Deane
Thanks M D, but Calculated Columns aren't possible with LiveConnection to Tabular Models, hence the measure route. – Mako

1 Answers

0
votes

You can accomplish this a few ways. Essentially, you need override the filter context so that the MAX function isn't just running over whatever slice you're showing in the visual. Using CALCULATE or the iterator function MAXX, set the wrap the table in the ALL() function to override the context and calculate max over all rows.

= CALCULATE(MAX([Calendar`Year]), ALL('Smithfield_Fiscal_Calendar'))

or

= MAXX(ALL('Smithfield_Fiscal_Calendar'), [Calendar`Year])

To get the breakout by date, you'll need to include a Date table in your model. PowerBI makes this possible with a few different DAX options. As an example, go to your Model tab, click 'New Table' and put in the following expression:

MyCalendar = CALENDAR(DATE(2019,1,1), DATE (2019,1,10))

This is a little trivial -- you'd want to use a useful range of dates but this one matches your example above. Next, add a column to [MyCalendar]

CalendarMonthYear = month([date]) & "-" & year([date])

Go to your budget table and add a similar field

BudgetMonthYear = month([date]) & "-" & year([date])

Go into your Model view and create a relationship between CalendarMonthYear and BudgetMonthYear. This will associate every date in the date table with the particular budget row from your budget table.

Hope it helps.