Forum Discussion

jcastrod's avatar
jcastrod
Regular Visitor
9 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
9 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
    9 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
      9 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?

    • CahabaData's avatar
      CahabaData
      Icon for Memorable Member rankMemorable Member
      9 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
        9 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?