Simple average from measure averages by category

[KristinaSp]

Hi,

I have the task, that is very simple in excel, but I cant figure out it in Powerbi desktop.

This is the data sample:

I need to calculate average profitability liek in the example.

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%

My measure DIVIDE (SUM(net profit), SUM(income)) gives me 0.19%, while I need to get 0.68%.

Please help me with this task.

Best wishes,

Kristina

[Ashish_Mathur]

Hi,

Write these measures:

Total income = SUM(Data[Income          ])

Total profit = SUM(Data[Net profit                 ])

Net profitability (%) = DIVIDE([Total profit],[Total income])

Measure = if(hasonevalue(Data[Place]),[Net profitability (%)],AVERAGEX(VALUES(Data[Place]),[Net profitability (%)]))

Hope this helps.

[v-stephen-msft · Accepted solution]

Hi @KristinaSp ,

You can create a new measure

new Net profitability = var _total=SUMX('Table',[Net profitability])
return IF(ISINSCOPE('Table'[Place]),[Net profitability],DIVIDE(_total,COUNT('Table'[Place])))

Best Regards,

Stephen Tao

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