Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
@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.
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.
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
@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.
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.
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!
@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.
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
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.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |