topic Re: Summing the totals of a sub-catergory in DAX Commands and Tips

Summing the totals of a sub-catergory

Anonymous — Mon, 27 May 2019 16:36:50 GMT

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	SubGroup	Sales
DACH	AT	100
DACH	DE	200
DACH	CH	400
BENELUX	NL	500
BENELUX	BE	200
BENELUX	LU	100
UKIR	UK	599
UKIR	IR	200

Is there any ways to do this?
Thanks

Re: Summing the totals of a sub-catergory

parry2k — Mon, 27 May 2019 22:08:15 GMT

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.

Re: Summing the totals of a sub-catergory

Anonymous — Tue, 28 May 2019 10:59:39 GMT

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

Re: Summing the totals of a sub-catergory

parry2k — Tue, 28 May 2019 11:35:18 GMT

Anonymous sure add a measure

Total Sales = SUM( Table[Sales])

now use this measure instead of sales column

Re: Summing the totals of a sub-catergory

Anonymous — Tue, 28 May 2019 12:08:48 GMT

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.

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

Re: Summing the totals of a sub-catergory

parry2k — Tue, 28 May 2019 14:57:03 GMT

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

Re: Summing the totals of a sub-catergory

Anonymous — Tue, 28 May 2019 15:08:29 GMT

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.

Re: Summing the totals of a sub-catergory

parry2k — Tue, 28 May 2019 15:08:13 GMT

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.

Re: Summing the totals of a sub-catergory

Anonymous — Tue, 28 May 2019 15:40:34 GMT

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-Group	Sub-Country	Revenue	SalesVolume Prior Year	SalesVolume Current Year	CustomCalculation
Group1	Sub-Country1	1000	500	600	1200
Group1	Sub-Country2	2000	700	650	1857
Group1	Sub-Country3	3500	800	900	3938	6995
	Grand Total	6500	2000	2150	6988

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!

Re: Summing the totals of a sub-catergory

parry2k — Tue, 28 May 2019 15:47:25 GMT

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] )

Re: Summing the totals of a sub-catergory

Anonymous — Tue, 28 May 2019 16:03:35 GMT

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.

Re: Summing the totals of a sub-catergory

parry2k — Tue, 28 May 2019 16:06:44 GMT

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

Re: Summing the totals of a sub-catergory

Anonymous — Tue, 02 Jul 2019 13:58:16 GMT

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.