Custom measure for single highest Y value in column/line chart

[Anonymous]

Been searching for months but didn't find a solution at all.

I want to create a chart with columns showing sales orders revenue, lines showing sales target revenue. Y-Axis is $ (axis 1=order $ axis 2 target $). X axis would contain time hierarchy (year->half year->quarter->week)

(measures in bold, fields in italic)

Problem is to identify a measure (no additional columns possible as I'm on a live connection) that would return the max value between target/revenue between all Years/Half Years/Quarters/Weeks to put in here:

so that the both axis would both be aligned to the highest single value (in this case 676M):

to make it harder, this should still work when drilling up/down so not possible to statically refer to a single field (would have been quarter in image above and week in image below):

I think the idea would be to pull two variables, one with single max value of orders, one with single max value of target and do a simple if A > B then A else B.

Any clue? Thanks!

[besomebody20 · Accepted solution]

Hi @Anonymous , 

See if this measure can get you pointed in the right direction. I created a sample data set and date table with the date hierarchy you described. This measure assumes the measures Orders and Target already exist.

Max Y-Axis Value = 
CALCULATE(
    SWITCH(
        TRUE(),
        HASONEVALUE(DimDate[Quarter]),
            MAXX(
                ADDCOLUMNS(
                    SUMMARIZE(
                        'DimDate',
                        'DimDate'[Week]
                    ),
                    "y axis val", MAX([Orders], [Target])
                ),
                [y axis val]
            ),
        HASONEVALUE(DimDate[Half Year Label]),
            MAXX(
                ADDCOLUMNS(
                    SUMMARIZE(
                        'DimDate',
                        'DimDate'[Quarter]
                    ),
                    "y axis val", MAX([Orders], [Target])
                ),
                [y axis val]
            ),
        HASONEVALUE(DimDate[Year]),
            MAXX(
                ADDCOLUMNS(
                     SUMMARIZE(
                         'DimDate',
                         'DimDate'[Half Year Label]
                     ),
                    "y axis val", MAX([Orders], [Target])
                ),
                [y axis val]
            ),
        MAXX(
            ADDCOLUMNS(
                SUMMARIZE(
                   'DimDate',
                   'DimDate'[Year]
                ),
                "y axis val", MAX([Orders], [Target])
            ),
            [y axis val]
        )
    ),
    ALLSELECTED('DimDate')
)

[Anonymous]

First of all: thanks, Logic is nearly perfect! I am struggling at learning how to work with tables in DAX as I find it very difficult to debug/troubleshoot as there's no visibility of intermediate steps/output; if you have any suggestion on how to learn getting better, I'm in 🙂

Technically I can consider my problem solved as the Y axis now scales automatically to values that make it easier to read the charts.
 

That said, the context requires some fine tuning which of course wasn't possible for you to see as you were helping on abstract data; let's have a look:

And here's what I think seems to me is happening:

or somethign like:

The context of the measure takes into account the max "parent" (Half year in this example) value of 891.21M rather than only looking at the max child level (Quarter) of 676.48M. which is why the Y axis value is set to 891.21M

My assumption: MAXX(table, expression) searches for the highest value within the entire table which returns 891.21M while we want to look only at the deepest level of values (rows Q3 and Q4). Any clue?

I'll wait some days before flagging previous answer as a solution in case we reach an optimized version (and I'll then flag that one, if available, as solution) 🙂

Thank you so much again!
Ale

[besomebody20]

Hi @Anonymous ,

I'm glad to hear the measure is nearly there. Hopefully we can get it working perfectly for you!

Trying to understand context in Power BI and how to anticipate what will be returned and why was difficult for me to grasp in the beginning. I believe it's one reason why many people get frustrated with Power BI. But once the we can understand how Power BI applies context to each DAX expression, Power BI can become much easier and very powerful.

First, let me explain what the measure does and show a few table visuals of the results of the measure.

As a prerequisite here is the date hierarchy I created to use for the drilldown capability:

In the measure, the logic checks if the bottom most level has one value. Then, checks the next level up until the default scenario is returned. I highlighted each condition to check to determine which grouping to return to find the MAX value of. 

The first condition to check is if Quarter has one value. If true, that means the chart has been drilled down to the lowest level, so return a table with a group for the Week. 

It will return this table and the measure will return as follows:

The next condition to check is if Half Year Label has one value. If true, the table is returned with a group for the Quarter. 

It will return this table and the measure will return as follows:

The remaining conditions will return the following tables:

The key to the measure working is whichever column is checked to have one value, return a table to MAXX with grouping of the next lowest column in the hierarchy.

If HASONEVALUE(DimDate[Quarter]) then group on 'DimDate'[Week]

If HASONEVALUE(DimDate[Half Year Label]) then group on 'DimDate'[Quarter]

If HASONEVALUE(DimDate[Year]) then group on 'DimDate'[Half Year Label]

else group on 'DimDate'[Year]

Next, to debug and troubleshoot, I will either create a visual with the columns used in an iteration function along with the measures added to the grouping, or I will use DAX Studio (DAX Studio | DAX Studio) to run the DAX expressions to see what is returned.

Let me know of any further questions.

David

[Anonymous]

Hi David, following up on this: is there anything more efficient we can use than hasonevalue? 

Because when we drill down from year to half year (or HY to quarter), there's not a single value of year but multiple ones so the max is calculated on the year (1.069+455?1,525) and not on the half year 1.069):

so that should be something that if existed I'd call HasLeastValues 🙂
which brings us back to my initial approach: take the highest value of (all) Revenue and highest value of (all) forecast and pick that highest one.

[Anonymous]

your first solution was just perfect! I simply had messed up while testing and the HASONVALUE evaluations were being evaluated in the wrong order (starting by year and not quarter) 😉 thank you very much! First answer flagged as solution 😉