locked
Find Percentage ( non-additive ) Month over Month and Year Over Year. RRS feed

  • Question

  • Hi,

    I am trying to have a Time Based Measure based on several columns to create a Ratio.  The formula on the ratio is simple: (a+b)/c.  Data is given in daily value.  A Calendar table is Marked for Time Intelligence in PowerPivot and relating to the daily level table below:

    Daily level:

    Year-Month Count_SodaSales_Buyer(a) Count_MilkSales_Buyer(b) Count_WaterSales_Buyer(c)
    2013-01-01 291 453 14,674
    2013-01-02 232 454 12,492
    2013-01-03 342 432 15,581
    2013-01-04 321 421 13,885
    2013-02-01 212 432 12,970
    2013-02-02 221 413 11,032
    2013-02-03 321 462 9,907



    At Month level, the result will be liked

    Year-Month Count_SodaSales_Buyer Count_MilkSales_Buyer Count_WaterSales_Buyer BuyerRatio
    2013-01 1,186 1,760 14,674 0.20076325
    2013-02 754 1,307 12,492 0.16498559

    It is also needed to be drill down from the Month to Daily level.

    What is the DAX formula do I need to do this?

    Thx.



    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Thursday, September 12, 2013 6:54 PM

Answers

  • Your month-level results for watersales look off.  Is there a reason you took the sum of soda and milk for the month, but took the 1/1 and 1/2 numbers for water?

    Assuming that's a typo, you should be able to create 4 simple calculated measure like this:

    SumSodaSales:=SUM([Count_SodaSales_Buyer(a)])
    SumMilkSales:=SUM([Count_MilkSales_Buyer(b)])
    SumWaterSales:=SUM([Count_WaterSales_Buyer(c)])
    Ratio:=([SumSodaSales] + [SumMilkSales]) / [SumWaterSales]

    With that, I get the following results:

    Let me know if that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Friday, September 13, 2013 5:07 AM
    Answerer
  • note to myself... :)

    ly sales:=calculate([sales],dateadd('date'[datekey],-1,year)


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    • Marked as answer by light_wt Monday, September 16, 2013 4:36 PM
    Monday, September 16, 2013 4:36 PM

All replies

  • Your month-level results for watersales look off.  Is there a reason you took the sum of soda and milk for the month, but took the 1/1 and 1/2 numbers for water?

    Assuming that's a typo, you should be able to create 4 simple calculated measure like this:

    SumSodaSales:=SUM([Count_SodaSales_Buyer(a)])
    SumMilkSales:=SUM([Count_MilkSales_Buyer(b)])
    SumWaterSales:=SUM([Count_WaterSales_Buyer(c)])
    Ratio:=([SumSodaSales] + [SumMilkSales]) / [SumWaterSales]

    With that, I get the following results:

    Let me know if that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Friday, September 13, 2013 5:07 AM
    Answerer
  • Guess, I had too much fluid and beer might had been influencing. :)  I'll check out in the morning and try it out.

    On a different note,  is the formula in another measure for the prior date?  This way, the current and the prior can drop into excel and drill down using the calendar? 

    Thanks.


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.


    • Edited by light_wt Friday, September 13, 2013 9:47 AM
    Friday, September 13, 2013 9:38 AM
  • note to myself... :)

    ly sales:=calculate([sales],dateadd('date'[datekey],-1,year)


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    • Marked as answer by light_wt Monday, September 16, 2013 4:36 PM
    Monday, September 16, 2013 4:36 PM