Data Aggregation - Wrong Total Subtotals

[_Melenios_]

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!

[Anonymous]

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

[_Melenios_]

Hey @Anonymous ,

That looks really promissing and i will invest some time for sure! Apreciatted you time!

Cheers!

[AlB · Accepted solution]

@_Melenios_ 

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 

[_Melenios_]

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!

[_Melenios_]

Hi again,

Thanks all for your fast replies. I've uploaded a draft of how my pbix looks like.

Example 

Cheers to all!

[AlB]

@_Melenios_ 

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 

[_Melenios_]

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 🙂

[AllisonKennedy]

@_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 

[AlB]

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 

[amitchandak]

@_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.