Forum Discussion
Data Aggregation - Wrong Total Subtotals
Hi all,
I'm trying to do a data aggregation based on any filter you select( Date,Week,Month etc). My problem is that i cannot get the correct % according to what i choose on the filters.(Eg. If i select only one day i want to see the % of sales splitted per hour, if i filter by month the % only for that month or as manyny as i have selected).
My min aggregation is based on an hour level, so what i want actually is to count how many hours did i had sales for a given date and divide by the sum of each day and repeat this perday,week etc)
I have one [Calendar] Table and one [Data] Table without any relationships.
So i try to count all hours that sales exists :
CountHours:=CALCULATE(COUNT(Hour]),FILTER(DATA,DATA[Sales]>0))
then sum based on the filters :
MYSales:=CALCULATE(SUMOFSales,ALLSELECTED([CALENDAR]))
and then
DIVIDE([CountHours],[MYSales],0)
My final table is like this but instead of having a Grand total of 100% and then the correct subtotals % per month,week,day, hour i get crazy values.
| Month | Week | Date | Hour | Sales | %Sales |
| November | 44 | 40 | 4950% | ||
| 45 | 40 | 63% | |||
| 46 | 40 | 63% | |||
| 47 | 41 | 56% | |||
| 48 | 41 | 47% | |||
| 49 | 4% | ||||
| November Total | 41 | 16% | |||
| Grand Total | 41 | 16% |
Any suggestion would be really appreciated!
i see. try this then
Sales perc V2 = DIVIDE ( SUM ( Table1[Sales] ), CALCULATE ( SUM ( Table1[Sales] ), ALLSELECTED () ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
10 Replies
Hi _Melenios_
Can you share the pbix by any chance?
You'd have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
_Melenios_ , these month , Week values are coming from Calendar table ?
Refer my video for % of total and Sun Total - https://www.youtube.com/watch?v=6jTildcV2ho
if that did not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi _Melenios_,
It sounds like a common total level calculation issue, please take a look at the following blog if it helps your scenario:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
Hey Anonymous ,
That looks really promissing and i will invest some time for sure! Apreciatted you time!
Cheers!
_Melenios_ I believe you will want a relationship between Calendar and Data table. Also, it looks like you're trying to divide total hours by sum sales (count divided by sum, and possibly also hours divided by $$??) which could be part of the problem for your crazy percentages.
What does the raw data model look like? Please can you paste some sample rows of both Data and Calendar tables? Or share pbix as already suggested by AlB
Hi again,
Thanks all for your fast replies. I've uploaded a draft of how my pbix looks like.
Cheers to all!
I think just using a matrix visual (instead of a table visual) and selecting
Show vale as -->Percent of Grand total
for your sales measure would do the trick (or most of it)?
See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Dear AlB ,
Thanks for you reply. Well i have already thought of the solution suggested but my problem here is that i need to have this as measure in order to use this "weighted %" for future calculations as well.
For example, if i need to do an estimation for next year sales for any given month i would just need to multiply the weight % that i already know for the given/past year with the SUM of my Expected Sales for that future month.
I hope it was clear 🙂
i see. try this then
Sales perc V2 = DIVIDE ( SUM ( Table1[Sales] ), CALCULATE ( SUM ( Table1[Sales] ), ALLSELECTED () ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Many many thanks AlB .
I feel kind of stupid that was something that simple but i'm still trying to learn what kind of "filtering" is needed on each case!
Cheers!
