Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
diogodasilva
Frequent Visitor

How to calculate subtotal sum of a measure?

Hi guys,

 

I have a measure (ROA) which is calculated by dividing revenue with clients NET position. I need a way to display this measure by summing the last 12 months from its result. 

 

diogodasilva_0-1662122886317.png

However as you can see on the total bottom right it is aggregating it by division from the total.

 
ROA Positivador =
DIVIDE(
    SUM('Historico Receita'[Valor Bruto Recebido]),
    SUM('Historico Positivador'[NetEmM])
)

 

Any ideas on how to solve this? Thanks in advance.

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @diogodasilva 

 

Do you want the total ROA to show the sum of divisions for the last 12 months? You can try the following methods.

Sample data:

vzhangti_0-1662544208776.png

Measure:

ROA = DIVIDE(SUM('Table'[Valor]),SUM('Table'[Net]))
Final ROA = 
Var N1=SUMMARIZE(FILTER(ALL('Table'),[Month]<=MAX('Table'[Month])),[Month],"Sum",[ROA])
Var N2=TOPN(12,N1,[Month],DESC)
Var Cumulative12month=SUMX(N2,[Sum])
return
IF(HASONEVALUE('Table'[Month]),[ROA],Cumulative12month)

vzhangti_1-1662544310860.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

 

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @diogodasilva 

 

Do you want the total ROA to show the sum of divisions for the last 12 months? You can try the following methods.

Sample data:

vzhangti_0-1662544208776.png

Measure:

ROA = DIVIDE(SUM('Table'[Valor]),SUM('Table'[Net]))
Final ROA = 
Var N1=SUMMARIZE(FILTER(ALL('Table'),[Month]<=MAX('Table'[Month])),[Month],"Sum",[ROA])
Var N2=TOPN(12,N1,[Month],DESC)
Var Cumulative12month=SUMX(N2,[Sum])
return
IF(HASONEVALUE('Table'[Month]),[ROA],Cumulative12month)

vzhangti_1-1662544310860.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

 

Greg_Deckler
Super User
Super User

@diogodasilva This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.