Max of a Calculated Measure

[Anonymous]

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 Information]

[Table 1]: Contains information on all transactions booked with an assets

Asset ID, Date, Turnover of transaction

[Table 2]: Contains information on the uptime of every assets on any given day

Asset ID, Date, Hours of Uptime

What I have I done: Calculated a measure 

Turnover per Uptime = SUM(Turnover of transaction) / SUM(Hours of Uptime)

What I need: A measure for every asset that calculate that asset's Turnover per Uptime in relation to the highest (max) Turnover per Uptime of any asset in the data base. So in easy terms: 

% of Highest Turnover per Uptime = Turnover per Uptime/ Highest Turnover per Uptime

The output I require would look like this:

Date Slicer

Table with the these columns

Asset ID, Turnover per Uptime, % of Highest Turnover per Uptime

I have tried the solution from here suggest by @Zubair_Muhammad  , but that doesn't produce what I need.

Who can help me?

[Anonymous]

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]

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]

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]

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]

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]

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]

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.

[Anonymous]

I have managed to include all dates with no transactions for assets. However, the formular you provided still calculates the max average turnover/availability ratio for each individual asset. I would need the highest ratio amongst all assets. Thoughts?

[Anonymous · Accepted solution]

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]

I will give this a try. Not sure I will manage. But thanks for your help!

[amitchandak]

@Anonymous 

refer if this can help

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

I think at the date level you need take max (maxx)of you formula

Or Over all total

Averagex(summarize(table,table[date],"_1",[Turnover per Uptime], "_2",maxx(all(Table),[Turnover per Uptime])),divide([_1],[_2]))

You add group bys as per need