Trying to get the largest values in a summary of a detail table.

[Anonymous]

Hi, I'm trying to pull out the Amount and the largest change in % between two dates from a table that contains Directory, Detail and date and place it in a card, based on the Directory chosen by the user.
I keep running into challenges with the code. Can you help?

For example, with this subset of the data, I would want to get the biggest amount of data on what day, and the largest % between two, as 2 seperate measures, and place each one in a separate card.

Here's my example,
User chooses Dir1 from a dropdown
the data on the left is the detail and the summary on the right is created from it. 

So what I'm trying to do is to capture a few measures.
* The largest summary and date, created from the detail 
* The largest % and the date of that % created from the detail

I keep trying to build filters and each one fails. 
Can I get some support on creating the filters that would be needed for this?  

Thanks, Stesven

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try to create measure with below dax formula:

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Date],
    'Table'[Directory],
    "Sumary", SUM ( 'Table'[Detail] )
)

% =
VAR dir =
    SELECTEDVALUE ( 'Table 2'[Directory] )
VAR cur_date =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR tmp =
    FILTER ( ALL ( 'Table 2' ), [Directory] = dir && [Date] < cur_date )
VAR pre_date =
    MAXX ( tmp, [Date] )
VAR _a =
    CALCULATE ( SUM ( 'Table 2'[Sumary] ), FILTER ( tmp, [Date] = pre_date ) )
VAR _b =
    CALCULATE (
        SUM ( 'Table 2'[Sumary] ),
        FILTER ( ALL ( 'Table 2' ), [Directory] = dir && [Date] = cur_date )
    )
RETURN
    DIVIDE ( _b - _a, _a, 0 )

Max % With Date =
VAR tmp =
    FILTER (
        ALL ( 'Table 2' ),
        [Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
    )
VAR _val =
    MAXX ( tmp, [%] )
VAR _date =
    CALCULATE (
        MAX ( 'Table 2'[Date] ),
        FILTER (
            ALL ( 'Table 2' ),
            [Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
                && [%] = _val
        )
    )
RETURN
    _date & ": "
        & FORMAT ( _val, "percent" )

Max Sumary With Date =
VAR tmp =
    FILTER (
        ALL ( 'Table 2' ),
        [Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
    )
VAR _val =
    MAXX ( tmp, [Sumary] )
VAR _date =
    CALCULATE (
        MAX ( 'Table 2'[Date] ),
        FILTER (
            ALL ( 'Table 2' ),
            [Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
                && [Sumary] = _val
        )
    )
RETURN
    _date & ": " & _val

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try to create measure with below dax formula:

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Date],
    'Table'[Directory],
    "Sumary", SUM ( 'Table'[Detail] )
)

% =
VAR dir =
    SELECTEDVALUE ( 'Table 2'[Directory] )
VAR cur_date =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR tmp =
    FILTER ( ALL ( 'Table 2' ), [Directory] = dir && [Date] < cur_date )
VAR pre_date =
    MAXX ( tmp, [Date] )
VAR _a =
    CALCULATE ( SUM ( 'Table 2'[Sumary] ), FILTER ( tmp, [Date] = pre_date ) )
VAR _b =
    CALCULATE (
        SUM ( 'Table 2'[Sumary] ),
        FILTER ( ALL ( 'Table 2' ), [Directory] = dir && [Date] = cur_date )
    )
RETURN
    DIVIDE ( _b - _a, _a, 0 )

Max % With Date =
VAR tmp =
    FILTER (
        ALL ( 'Table 2' ),
        [Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
    )
VAR _val =
    MAXX ( tmp, [%] )
VAR _date =
    CALCULATE (
        MAX ( 'Table 2'[Date] ),
        FILTER (
            ALL ( 'Table 2' ),
            [Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
                && [%] = _val
        )
    )
RETURN
    _date & ": "
        & FORMAT ( _val, "percent" )

Max Sumary With Date =
VAR tmp =
    FILTER (
        ALL ( 'Table 2' ),
        [Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
    )
VAR _val =
    MAXX ( tmp, [Sumary] )
VAR _date =
    CALCULATE (
        MAX ( 'Table 2'[Date] ),
        FILTER (
            ALL ( 'Table 2' ),
            [Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
                && [Sumary] = _val
        )
    )
RETURN
    _date & ": " & _val

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

This was excellent! It not only literally gave me the solution, it allowed me to store this as a template going forward for future endeavors like this. 

I really appreciate your help and it gave me the answer I was looking for.  IT's just too bad that there isn't a VBA like code that code have been used.  In Excel this would have been so simple to do.

Thanks!