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 

 

I believe you can use the methodology below:

I am assuming that you have a measure called [Turnover / uptime].

The measure below can then be used as the denominator for your measure [% of max turnover/uptime]

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"; [Turnover / uptime]
            );
            [Value]
        );
        ALLSELECTED( AssetInformation);
        ALLSELECTED( 'Calendar')
    )
)

 

If the measure above works as requested then please mark it as the accepted solution. 

Kudos is appreciated.

 

BR

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

     

    this looks really good and logic. Close to what I tried before. Unfortunately it produces 'Infinity' as a value. Any thoughts?

     

    I do need to filter out certain assets that might produce strange values, will this measure be responsive to filtering out certain asset_ids ?

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