Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Max of a Calculated Measure

Hi,   I have the following situation: I have two tables with information on assets. The two tables are linked via two tables: [Date Table] and a Table containing information on every asset [Asset I...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)))

     

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous 

 

If you make a slicer or a filter on the page with the assets you wish to include then it adapts.

 

If you use the DIVIDE() function then you can manually insert a value for 'errors', i.e. blank or 0.  DIVIDE(<numerator>, <denominator> [,<alternateresult>])

https://docs.microsoft.com/en-us/dax/divide-function-dax

 

BR

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.