Forum Discussion
% of Total by Week
Thanks for the quick response!
I've thought of 2 things since reviewing your post:
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?
2. I've re-read my question, and think I presented my problem incorrectly. My data is really much more like as follows:
| Week | ID | Value | Category |
| 1/1/2016 | 1 | 10 | A |
| 1/1/2016 | 2 | 20 | A |
| 1/1/2016 | 3 | 20 | B |
| 1/1/2016 | 4 | 50 | C |
| 1/8/2016 | 5 | 90 | C |
| 1/8/2016 | 6 | 100 | C |
| 1/8/2016 | 7 | 10 | A |
| 1/8/2016 | 8 | 20 | B |
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?
I really appreciate your help!
Microsoft EmployeeWhat 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 you should be able to use Line Chart to show it on the report.
Regards
v-ljerr-msft, thank you very much! This is so close to the solution I need.
I have double checked my formula to match yours, however, the values I'm getting are greater than 1...however, when I sum up for a given week the result divide by, say, one category's value, it comes out to the correct percentage. Does that make sense?
So for one week I get A = 4.2, B = 2.2, C = 1.1, which is odd. However, 4.2/(4.2+2.2+1.1) is the correct value I'm trying to display, so I know this is close...any thoughts?
- v-ljerr-msft
I have double checked my formula to match yours, however, the values I'm getting are greater than 1...
So could you share your formula and some sample data which can reproduce this issue in your case?
Regards