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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Summing the totals of a sub-catergory

Hi all,

 

With the below table, I want to create a measure that sums the total of sub-groups when the Main Group is selected . 

 

I.E when DACH is selected, I want it to sum up the sub-totals of the AT, DE and CH levels first, then sum up the totals of those sub-totals.  (This is due to the measure calculating sales differing per sub-group)

 

 

CountryGroup SubGroupSales
DACHAT100
DACHDE200
DACHCH400
BENELUXNL500
BENELUXBE200
BENELUXLU100
UKIRUK599
UKIRIR200


Is there any ways to do this? 
Thanks

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello,

I'm posting this if anyone else gets the same problem. In the end i solded it by using the following measure:

 

SUMX(VALUES(TABLE[Sub-Country]), CustomCalculation)).

 

Hope it helps. 

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

Hello,

I'm posting this if anyone else gets the same problem. In the end i solded it by using the following measure:

 

SUMX(VALUES(TABLE[Sub-Country]), CustomCalculation)).

 

Hope it helps. 

parry2k
Super User
Super User

@Anonymous it should be straight forward, just put subgroup and sales to table visual and use countrygroup on slicer.

 

If this is not what you asked for, just provide what actual output you are looking for.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi parry2k,

 

Thanks for the tip.  It works if I use this method.

 

However, is there a way to do it only using a measure?

 

Thanks, sunnysideup

@Anonymous sure add a measure

 

Total Sales = SUM( Table[Sales])

now use this measure instead of sales column

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi parry2k, 


Thanks for you speedy response.

 

So what I actually want to do with this measure is for it to for example: calculate the sales for Austria, then for Germany , then for Zwitserland and finally show the total of those three countries.

 

I need this because the calculation formula of sales differs per agrregation levels. I thought of doing "CALCULATE([sales],GROUPBY(CountryGrouping,[SubGroup]))", but it's not quite right.


Rows_PNG.PNG




This picture illustrates my issue better, if we sum up the totals of 'sales' per country, we get 6233, however, the grand total shows 6240. 

 

 

Do you have any ideas? 
Thanks

@Anonymous not sure why you are doing groupby, did you just use the measure I provided? you are over complicating the problem.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi parry2k, thanks for your reply.

 

Simply caluclating the sum of sales is not exactly what I'm going for. I need the subcategories summed first then the total sum of those sub-totals.

Please see the image in my previous reply for what I'm trying to achieve.  If I do a simple calculation of the total sales, it will not give me the number I'm trying to reflect.

 

Thanks again for you help with this. 

@Anonymous based on sample data you provided in original post, let me know what would you like to see, it is not at all clear what you are trying to achieve.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi, Applogies for not being clear.  Please see below a more detailed example data set.

For a report I use a CustomCalculation defined as "( Revenue / SalesVolume Prior Year) * SalesVolume Current Year".

The result of this calculation can be seen in the collumn CustomCalculation. 

Country-GroupSub-CountryRevenueSalesVolume Prior YearSalesVolume Current YearCustomCalculation 
Group1Sub-Country110005006001200 
Group1Sub-Country220007006501857 
Group1Sub-Country3350080090039386995
 Grand Total6500200021506988 


If you sum up the grand total of CustomCalculation, you will get 6988. However, if you sum-up the totals per Sub-Country, you'll get 6995.

 

 

If I create the following dax measure to calculate the total of the CustomCalculation on "Group1" level, it will give me the result  6988.
However, I need the measure to return the sum of the sub-totals of the sub-countries, so 6995.

So the measure that I want to create for total CustomCalculation for "Group1" should equal sub-total of Sub-Country1 + sub-total of Sub-Country2 + sub-total of Sub-Country3 =  6995.

I hope this explains in more detail. Please let me know if you have more questions.

Thank you so much!

@Anonymous this is much better and totally different what you originally posted.

 

here is the measure you need to add

 

Custom = SUMX( Table2, DIVIDE(Table2[Revenue], Table2[SalesVolume Prior Year] ) * Table2[SalesVolume Current Year] ) 


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Thanks parry2k for your help.

 

I tried this measure before and I only get the grand total.  

 

Like I stated before, this is not what I'm going for.

For example, In SQL I run a sub-query that first calculates the subtotal of Sub-Country1 + sub-total of Sub-Country2 + sub-total of Sub-Country3. And then the main query will return the total of that sub-query. 

I want to repllicate this is DAX. Because again, I'm not looking for the grand-total of 6988 but the sum of the sub-totals 6995.


Thanks again. 

@Anonymous I'm getting correct result. there is something else which could be the reason why it is not working at your end. Either share your pbix file for me to take a look otherwise I don't know how else to help

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors