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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
SerG
Helper I
Helper I

DAX, Find MAX value of a Measure

Hi Everyone!

I'm having issue to solve one problem. 

I'm having next report:

 Report.jpg

 

Sales[Percent] is a Measure:

Percent = SUM(Sales[Sum])/SUM(FilialsSales[Sum])

 

Okay, it works.

Now I need to find MAX value of Sales[Percent] (it will be 0,198).. And devide each Percent value by MAX value of [Percent]:

0,184/0,198; 0,157/0,198; 0,038/0,198; .... etc

But I don't know how to find MAX.. Is it really? Help me please! =(

Maybe I chose wrong way?((

 

P.S.

Data of tables(not full, only part):

Sales :

Sales.jpg

 

FilialSales:

FilialSales.jpg

1 ACCEPTED SOLUTION

Hi @SerG

 

Try this one

 

Percent / Max Percent =
DIVIDE (
    [Percent],
    MAXX (
        ADDCOLUMNS (
            SUMMARIZE (
                ALLSELECTED ( Sales ),
                [AdminName],
                Cities[City],
                "SUM", SUM ( Sales[Sum] )
            ),
            "Percent", CALCULATE ( SUM ( Sales[Sum] ) ) / CALCULATE ( SUM ( FilialsSales[Sum] ) )
        ),
        [Percent]
    )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

13 REPLIES 13
judi4688
Frequent Visitor

Hi, I'm having a similar problem.

 

I have items with a profit margin. I calculated via measure the rank of the profit margin and would now like to divide the rank number by the maximum number of rank.

 

Any hints?

v-shex-msft
Community Support
Community Support

Hi @SerG,

 

If you can please share this file to onedrive/google drive, I can't get your file through that link.

In my opinion, I'd like to suggest you to create a summary table as variable in your measure, then use it to result the max percent.

 

Sample: not so sure if it suitable for your situation.

Max Percent =
SUMX (
    SUMMARIZE (
        ALLSELECTED ( Table ),
        Table[AdminName],
        Table[City],
        "SUM",SUM(Table[SUM]),
        "Percent", [Percent]
    ),
    [Percent]
)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

Unfortunately your formula doesn't work for my situation =(

Hi @SerG,

 

I modify my formula to summary calculated result as the variable table, then use maxx function to get the max result from summary table.

Max Percent =
MAXX (
    SUMMARIZE (
        ALLSELECTED ( Sales ),
        [AdminName],
        [City],
        "SUM Sales", SUM ( [Sum] ),
        "SUM Filials", SUMX (
            FILTER ( ALL ( FilialsSales ), FilialsSales[City] = Sales[City] ),
            FilialsSales[Sum]
        )
    ),
    [SUM Sales] / [SUM Filials]
)

9.PNG

 

Notice: if your data contains any privacy data, please do mask sensitive data before sharing.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

Thanks!!

But it doesn't work, when I'm changing a Period =(((

Hi @SerG

 

Try this one

 

Percent / Max Percent =
DIVIDE (
    [Percent],
    MAXX (
        ADDCOLUMNS (
            SUMMARIZE (
                ALLSELECTED ( Sales ),
                [AdminName],
                Cities[City],
                "SUM", SUM ( Sales[Sum] )
            ),
            "Percent", CALCULATE ( SUM ( Sales[Sum] ) ) / CALCULATE ( SUM ( FilialsSales[Sum] ) )
        ),
        [Percent]
    )
)

Regards
Zubair

Please try my custom visuals

@v-shex-msft

Thank U very much!! You helped me a lot! It's awesome 😃

SerG
Helper I
Helper I

Hi! Here is the link to download the pbix file: http://dropmefiles.com/wKFH1

Ashish_Mathur
Super User
Super User

Hi,

 

Share the link from where i can download the pbix file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi! Here is the link to download the pbix file: 

dropmefiles\.com\/wKFH1

PS

Remove "\" from the link

Hi,

 

I get a message saying that the file is corrupt.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.