DAX measure - show values from best day

1
votes

I have, in PowerBI (October 2017 release) some data showing the snapshotted output of some solar panels at five-minute intervals.

'Solar Output' table containing three columns: Production (KW): numeric, Date: date, Time: time

I presently have a measure which calculates the total production for each day by getting a sum of the instantaneous readings for the day and dividing by 12 (because there are 12 readings per hour) and then shows the average of this daily amount over whichever period is in the current context:

Avg Daily Production (KWh) = 
    AVERAGEX(SUMMARIZE('Solar Output', 'Solar Output'[Date], "Daily Production (KWh)", SUM('Solar Output'[Production (KW)]/12), [Daily Production (KWh)])

For my visualisations I'd like to show a chart which displays the average production at various times of the day compared to the production at that time for the best day, i.e. the day with the highest value for [Avg Daily Production (KWh)].

Here is my working so far:

Identify the top day by using

Best Day = TOPN(1, SUMMARIZE('Solar Output', 'Solar Output'[Date], "Daily Production (KWh)", [Avg Daily Production (KWh)]), [Daily Production (KWh)], DESC)

But this produces a row, not a scalar, and I can't quite work out how to just get this date and use it as a filter for a measure like this:

Production On Best Day (KW) = 
    CALCULATE(SUM([Production (KW)]), FILTER(ALL('Solar Output'[Date]), 'Solar Output'[Date] = [Best Day]))

desired solution

The chart I want would look like this, with the average production for the selected dates in green and then another series showing the production at those times on the best day pencilled in red.

2
powerbidax
Do you mind sharing a sample .pbix file / dataset? – Foxan Ng
Unfamiliar with posting files here, I will just learn how... – mendosi
Sorry, I have issues connecting to file hosting sites at work. Similar data can be found here pvoutput.org/… – mendosi
I think the FIRSTNONBLANK function would work, e.g. wrapped around your Best Day expression. I'm not 100% certain and dont have time to play around with it today, but HTH. – Mike Honey
@MikeHoney FIRSTNONBLANK(ColumnName, Expression) requires a column reference as an argument but TOPN() returns a table... – mendosi

2 Answers

2
votes

Can't really get my head around the calculations involved but I hope I get your requirement.

So you want to plot two lines on the same chart, one always being the data from the best day as reference, and the other one the data selected from the date filter, right?

You're actually pretty closed to the solution with the info given above.

I just modified your DAX to:

Production On Best Day (KW) = 
CALCULATE(
    SUM('Solar Output'[Average]),
    FILTER(
        ALL('Solar Output'[Date]),
        'Solar Output'[Date] = VALUES('Best Day'[Date])
    )
)

(Not quite sure how you compute your intermediate measures so you may need to adjust according to your measures)

Yes, TOPN() returns a table, so you can get the date value using VALUES('Best Day'[Date]) and use it for comparison.

Results:

result1 result2

0
votes

Alternate approach to solve this issue is to use the MAXX() function to get a scalar value out of the the table returned by TOPN().

This works where MAX() doesn't because MAXX() accepts a table expression as its first argument (the table expression is the result of the TOPN() function) where MAX() only takes columns from existing tables.

Best Day = MAXX(TOPN(1, 
                     SUMMARIZE(ALL('Solar Output'), 
                               'Solar Output'[Date], 
                               "Daily Production (KWh)", 
                               [Avg Daily Production (KWh)]), 
                     [Daily Production (KWh)], 
                     DESC), 
                [Date])

The benefit of this approach is that it should work in versions of DAX with no calculated table support (e.g. SSAS 2014, Excel 2013).