% of Total by Week

Question

Hey guys,&nbsp;I've been digging around the forums today to try and find a solution, but can't quite find what I'm looking for.&nbsp;I'm trying to make a sparkline of 3 categories of data and their "% contribution" to the total for each week (my x-axis).&nbsp;The following is an example of my data:&nbsp;WeekCategoryValue1/1/2016A101/1/2016B201/1/2016C701/8/2016A701/8/2016B201/8/2016C10&nbsp;&nbsp;I want to be able to have for the first week Category A = 10%, B = 20%, C = 70%, and for second week Category A = 70%, etc...&nbsp;Using the "Percent of Grand Total" quick calc gives me the percent of the whole, not the week, which isn't helpful for my graph.&nbsp;Anyone have any guidance or suggestions? I've considered a DAX formula possibly helping in this case.&nbsp;I already forsee my future problem of actually charting it when I want 3 separate sparklines, one of each category, and that breaking the formula we put together in this post but...for another day!&nbsp;Thanks!

v-ljerr-msft · Answer

jcastrodWhat I'm actually driving towards is understanding my contributions of the categories, not by the categories. So in the data above, the value field is a means to drive the Category column (a conditional column I've established i.e. "If above x then A if above y then B etc..."). So what I would want to see in my week 1 data point is A = 50%, B = 25%, C = 25%, Week 2 etc... Does that make sense?You can create a measure like below.ContributionsOfCategories =
COUNTA ( TestTable[Category] )
    / CALCULATE (
        COUNTA ( TestTable[Week] ),
        ALLEXCEPT ( TestTable, TestTable[Week] )
    )Then&nbsp;you should be able to use Line Chart to show it on the report.Regards

greg_deckler · Answer

Create a measure like this:&nbsp;Measure = SUM([Value]) / SUMX(ALLEXCEPT(WeeklyCategories,WeeklyCategories[Week]),[Value])Then a visual like this:&nbsp;

jcastrod · Answer

Thanks for the quick response!&nbsp;I've thought of 2 things since reviewing your post:&nbsp;1. It seems as though the formula you presented isn't given the correct percentages by week. If you see, C in week 2 is below 10%, when it should be 10% on the dot. It seems as though they are cut in half?&nbsp;2. I've re-read my question, and think I presented my problem incorrectly. My data is really much more like as follows:WeekIDValueCategory1/1/2016110A1/1/2016220A1/1/2016320B1/1/2016450C1/8/2016590C1/8/20166100C1/8/2016710A1/8/2016820B&nbsp;What I'm actually driving towards is understanding my contributions of the categories, not by the categories. So in the data above, the value field is a means to drive the Category column (a conditional column I've established i.e. "If above x then A if above y then B etc..."). So what I would want to see in my week 1 data point is A = 50%, B = 25%, C = 25%, Week 2 etc... Does that make sense?&nbsp;I really appreciate your help!

cahabadata · Answer

it is a row count by category you seek.&nbsp;&nbsp; Is the total count for a week always 4 as per your example....or must the week row count also be established?&nbsp;

jcastrod · Answer

I believe a "count by week" must be established...if we can get that, then I think I can use a calc field to get my %total by category for each week, correct?

Forum Discussion

% of Total by Week

8 Replies

Related content

Calculate previous week total and previous 2 weeks total

Running totals per week

Total Trade per Week

Rolling 4 Week Total

Cumulative Total

Week	Category	Value
1/1/2016	A	10
1/1/2016	B	20
1/1/2016	C	70
1/8/2016	A	70
1/8/2016	B	20
1/8/2016	C	10