max function

[AndreaRSMRCT]

Hello community,

I would like to understand how it is possible to calculate the maximum product's % margin value. The situation is the following:

I have several kind of products (TIPO_PRO_COD), each one of them can be sold with different prices in different years (due to different the amount of hours spent for their making of).

I have, for each product, the following columns:

- selling price (PRODOTTO_IMPORTO),

- product margin (PRODOTTO_MARGINE),

In order to calculate the % product margin i used the following formula:

SUM(PRODOTTO_MARGINE) / SUM(PRODOTTO_IMPORTO)

I have set a Year filter and I have already selected the 2019 value (field name YEAR).

Now I would like to calculate the maximum % margin value. The value should not change when I select different products, categories, ecc. It must change only when I select a new Year.

Looking at the picture, I would like to find a measure that gives me 73.07% as the maximum Margine% value (Even if i select a different product (prodotto), or different product category, ecc ecc.)

I have tried to use the following formula but I don't know what is the correct sintax for the "Filter".

Max_Margine% = MAXX(CALCULATETABLE(VALUES(PRODOTTI[Tipo_Pro_Cod]),???????),sum(PRODOTTI[Prodotto_Margine])/SUM(PRODOTTI[Prodotto_Importo]))

Thanks in advance,

Andrea

[Mariusz]

Hi @AndreaRSMRCT 

Try somthing like this.

Measure = 
CALCULATE(
    MAXX(YourTable, your margin cal expression),
    ALLEXCEPT(YourTable, YourDateTable or YearColumn)
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AndreaRSMRCT]

Hello Mariusz,

Thank you very much for your reply.

With your formula the calculation is made on the row context. To be more specific, I have this situation:

Prodotto   Importo     Margine    Margine%

A                 100            20                 20%

A                 200            50                 25%

A                 150            60                 40%

B                  80             20                 25%

B                  100           10                 10%

.... 

The Margine% value is a measure calculated as:

SUM(Margine)/SUM(Importo)

When i try to use your MAXX formula I receive, as ouput, the third value (40%), instead i would like to receive the following result (29%):

Prodotto   Importo     Margine    Margine%

A                 450           130                29%

B                 180             30                 17%

Is there a way to create a table like these in the last example (inside the MAXX formula) and then calculated the maximum Margine% on it?

Andrea

[Mariusz]

HI @AndreaRSMRCT 

Try this.

MAXX(
    ALLSELECTED(PRODOTTI[Prodotto]),
    SUM(PRODOTTI[Prodotto_Margine]) / SUM(PRODOTTI[Prodotto_Importo])
)
Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AndreaRSMRCT]

Hi Mariusz,

I really appreciated your reply, and I also tried your solution but I didn't understand how that works. I'm looking for a measure able to give me a unique value considering what I have written before. A value, for example, that i can insert in a card visualization.

If i use a ALLSELECTED() I will have a measure that changes any time I select a different product.

Instead, i need to know if it is the possibility (with power bi) to aggregate the IMPORTO (sales amount) and MARGINE (Margin) for each unique PRODUCT CODE, then calculate for each unique PRODUCT CODE the percentage margin, and then calculate the maximum of percentage margin.

And this value should not change when i select different products, category, ecc, but it has to change only when i select a different year.

Best Regards,

Andrea

[Mariusz]

Hi @AndreaRSMRCT 

Please can you provide a cleare data sample with the outcome that you desire.

Thanks 
Mariusz

[Mariusz]

Hi @AndreaRSMRCT 

You can use google drive or something and paste the link here or just create a quick sample in excel and paste it here .

Hope this helps
Mariusz

[AndreaRSMRCT]

Is there a way, here on the community, to share a sample file?

Excuse me for this silly question, but I'm a new user of power bi software.

Andrea

[AndreaRSMRCT]

https://www.dropbox.com/s/2ddncx689q3tugv/Community.pbix?dl=0

I put a sample here.

There will be a card visualization with a sample measure. What i would like to have is the same card with the max argine% that you can see in the over table (73.07%).

And that value should remain the same even if I select another product.

Best Regards,

Andrea

[Mariusz · Accepted solution]

Hi @AndreaRSMRCT ,

Please see the below.

Prova = MAXX(
    ADDCOLUMNS(
        ALL(TIPO_PROD[Tipo_Pro_Des]),
        "Margin" , CALCULATE([Mis_Margine%], PROGETTO[Progetto_Stato] <> "Sospeso")
    ),
   [Margin]
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.