Skip to main content
cancel
Showing results for 
Search instead 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

Reply
KristinaSp
Helper I
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%

 

PlaceIncome        Net    profit          Net     profitability       
A1000101,00%
B2000201,00%
C1500040,03%
Total18000340,19%
    
I need: (1+1+0,03)/3 =0,68%

 

Best wishes and thank you in advance.

 

Kristina

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
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] ) )
    )
)

Screenshot 2021-11-06 162139.png


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!

View solution in original post

3 REPLIES 3
CNENFRNL
Community Champion
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] ) )
    )
)

Screenshot 2021-11-06 162139.png


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!

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.

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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