# Find Percentage ( non-additive ) Month over Month and Year Over Year.

• ### 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

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

• 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)])
Ratio:=([SumSodaSales] + [SumMilkSales]) / [SumWaterSales]```

With that, I get the following results:

Let me know if that helps.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

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

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

• Marked as answer by 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)])
Ratio:=([SumSodaSales] + [SumMilkSales]) / [SumWaterSales]```

With that, I get the following results:

Let me know if that helps.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

Friday, September 13, 2013 5:07 AM
• 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 Friday, September 13, 2013 9:47 AM
Friday, September 13, 2013 9:38 AM
• note to myself... :)