DAX, Find MAX value of a Measure

[SerG]

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:

[judi4688]

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]

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

[SerG]

@v-shex-msft

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

[v-shex-msft]

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]
)

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

Regards,

Xiaoxin Sheng

[SerG]

@v-shex-msft

Thanks!!

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

[Zubair_Muhammad · 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]
    )
)

[Zubair_Muhammad]

@SerG

See attached file as well

[SerG]

@v-shex-msft

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

[SerG]

Hi @v-shex-msft !

https://drive.google.com/open?id=0B0pwU1fEMgWPUU9sWnVMS1RvbEU

[SerG]

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

[Ashish_Mathur]

Hi,

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

[SerG]

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

dropmefiles\.com\/wKFH1

PS

Remove "\" from the link

[Ashish_Mathur]

Hi,

I get a message saying that the file is corrupt.