cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Average profitability by category vs total average profitability

Hello,

Could you help me with this issue. I need to calculate total average profitability like in the example below.

I use this measure and it is fine for distinct category: net profitability = DIVIDE (sum(net profit)/ sum(income)).

But the total result is 0.19%, while I need to get 0.68%

 Place Income Net    profit Net     profitability A 1000 10 1,00% B 2000 20 1,00% C 15000 4 0,03% Total 18000 34 0,19% I need: (1+1+0,03)/3 = 0,68%

Best wishes and thank you in advance.

Kristina

1 ACCEPTED SOLUTION
Community Champion
``````NP =
IF(
HASONEVALUE( Table1[Place] ),
SUM( Table1[Net profit] ) / SUM( Table1[Income] ),
AVERAGEX(
VALUES( Table1[Place] ),
CALCULATE( SUM( Table1[Net profit] ) / SUM( Table1[Income] ) )
)
)``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
3 REPLIES 3
Community Champion
``````NP =
IF(
HASONEVALUE( Table1[Place] ),
SUM( Table1[Net profit] ) / SUM( Table1[Income] ),
AVERAGEX(
VALUES( Table1[Place] ),
CALCULATE( SUM( Table1[Net profit] ) / SUM( Table1[Income] ) )
)
)``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Super User

I think you can just use the AVERAGEX part (remove the IF HASONEVALUE case) since averaging over a single place doesn't change the value.

Helper I

Thank you! A little bit modified and it worked perfect 🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors