Forum Discussion

jcastrod's avatar
jcastrod
Regular Visitor
10 years ago

% of Total by Week

Hey guys,   I've been digging around the forums today to try and find a solution, but can't quite find what I'm looking for.   I'm trying to make a sparkline of 3 categories of data and their "% ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

Create a measure like this:

 

Measure = SUM([Value]) / SUMX(ALLEXCEPT(WeeklyCategories,WeeklyCategories[Week]),[Value])

Then a visual like this:

 

jcastrod's avatar
jcastrod
Regular Visitor
10 years ago

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:

WeekIDValueCategory
1/1/2016110A
1/1/2016220A
1/1/2016320B
1/1/2016450C
1/8/2016590C
1/8/20166100C
1/8/2016710A
1/8/2016820B

 

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!

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    jcastrod

    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?

    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

    • jcastrod's avatar
      jcastrod
      Regular Visitor
      9 years ago

      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's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        jcastrod

        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

  • CahabaData's avatar
    CahabaData
    Icon for Memorable Member rankMemorable Member
    10 years ago

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

     

    • jcastrod's avatar
      jcastrod
      Regular Visitor
      10 years ago

      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?