Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

% Sales contribution calculation Weekly

Hi,

 

I need to calculate the percentage of weekly sales contribution.

Sales contribution= W1/W1+W2+W3+W4+W5 

 

In the calculation the slicer should filter the sales and total contribution should be always 100% upon multiple month or groupcode selection.

 

Ex- W1, W2, W3, W4, W5 sales of Jan should 100% and Dec should be 100% individually even if multiple months are selected. The slicer should also function based upon multiple selection of Groupcode and month also.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    The context of where you use the measure will be important.  Because of the "ALLEXCEPT" line, if you give it a context where both JAN and FEB exist in the same context, your denominator is going to be JAN + FEB.

    I'd expect you'd want to use this measure on a table where you have row contexts for JAN and FEB separately.

    If you are selecting a context where JAN and FEB are both present, how are you expecting to aggregate the output?  Like if Jan was 99% and Feb was 90%, what total value would you expect your measure to produce?

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I'm assuming that GroupCode needs to be kept in your context filters.  If i understand what you are asking for, you want this?

    Contribution Sales = 
var weeklySales = SUM('Sales Contribution'[Sales])
var totalMonthSales = CALCULATE(
    SUM('Sales Contribution'[Sales]),
    ALLEXCEPT('Sales Contribution'[GroupCode]), 'Sales Contribution'[MonthYear])
)
RETURN
DIVIDE(weeklySales, totalMonthSales)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Anonymous 

       

      Now using your measure, 

       

      Upon multiple year selection, the sum of % contribution of Jan & Dec Individually is not 100%.

      Instead, it is 100% upon summation of both months.

       

      Expectation: % contribution sales of Jan should be 100% and Dec should also be 100% contribution.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        The context of where you use the measure will be important.  Because of the "ALLEXCEPT" line, if you give it a context where both JAN and FEB exist in the same context, your denominator is going to be JAN + FEB.

        I'd expect you'd want to use this measure on a table where you have row contexts for JAN and FEB separately.

        If you are selecting a context where JAN and FEB are both present, how are you expecting to aggregate the output?  Like if Jan was 99% and Feb was 90%, what total value would you expect your measure to produce?