Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MRUry7
Resolver II
Resolver II

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

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

MRUry7_5-1687878173243.png

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




 







2 ACCEPTED SOLUTIONS
v-binbinyu-msft
Community Support
Community Support

Hi @MRUry7 ,

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

vbinbinyumsft_0-1688019242160.png

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.

View solution in original post

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!

View solution in original post

2 REPLIES 2
v-binbinyu-msft
Community Support
Community Support

Hi @MRUry7 ,

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

vbinbinyumsft_0-1688019242160.png

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.

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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors