Forum Discussion
Max of a Calculated Measure
Update: I have changed the data structure slightly by creating one table that holds all information on turnover and uptime by Date and Asset and was then able to calculate the maximum of the average using this code:
Maximum of averages = CALCULATE(MAXX( KEEPFILTERS(VALUES(Table[Asset])), CALCULATE(AVERAGE(Table[Turnover])) ), ALLSELECTED('Asset information'), ALLSELECTED(Date Table)))
Cheers! I have figured out what happens. It returns the highest Turnover per Availability on any given day in the selected period of time. I guess what would make more sense for me is the Highest Average Turnover per Availability over the entire seleted period.
How would I need to adjust the formular to achieve that? Sorry I am bit out of my depth here.
Arrh okay. I think you can solve it in two steps:
Avg per asset for all selected dates =
IF(
ISBLANK( [Turnover / uptime]);
BLANK();
CALCULATE(
AVERAGEX(
SUMMARIZE(
Table1;
AssetInformation[AssetID];
"Value"; [Turnover / uptime]
);
[Value]
);
ALLSELECTED( 'Calendar')
)
)
And then adjust you current measure:
Max Turnover/uptime =
VAR __turnoverUptime = [Turnover / uptime]
RETURN
IF(
ISBLANK( __turnoverUptime);
-- If the asset wasn't running the given day then it returns blank
BLANK();
-- Calculating the max turnover/uptime for all selected assets for all selected dates
CALCULATE(
MAXX(
SUMMARIZE(
Table1;
AssetInformation[AssetID];
'Calendar'[Date];
"Value"; [Avg per asset for all selected dates]
);
[Value]
);
ALLSELECTED( AssetInformation);
ALLSELECTED( 'Calendar')
)
)
I hope this helps
Yes it helps! The problem that remains is that days on which no transaction was booked on an asset aren't taken account when calculating the average over all days for that asset. This seems way more difficult than I imagined it would be
Hmm, then I would impute the values into the dataset. I.e. all days with no transactions have the value 0. This would also remove bias in your sampling and thereby in your calculations.
I will give this a try. Not sure I will manage. But thanks for your help!