cancel
Showing results 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

Helper I

## DAX, Find MAX value of a Measure

Hi Everyone!

I'm having issue to solve one problem.

I'm having next report:

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 :

FilialSales:

1 ACCEPTED SOLUTION
Community Champion

Hi @SerG

Try this one

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

Regards
Zubair

13 REPLIES 13
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?

Community Support

Hi @SerG,

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[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.
Helper I

@v-shex-msft

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

Community Support

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 ),
[City],
"SUM Sales", SUM ( [Sum] ),
"SUM Filials", SUMX (
FILTER ( ALL ( FilialsSales ), FilialsSales[City] = Sales[City] ),
FilialsSales[Sum]
)
),
[SUM Sales] / [SUM Filials]
)
```

Regards,

Xiaoxin Sheng

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

@v-shex-msft

Thanks!!

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

Community Champion

Hi @SerG

Try this one

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

Regards
Zubair

Community Champion

@SerG

See attached file as well

Regards
Zubair

Helper I

@v-shex-msft

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

Helper I
Helper I

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

dropmefiles\.com\/wKFH1

PS

Super User

Hi,

I get a message saying that the file is corrupt.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors