Answered by:
% over total wrong calc?
Question

Lets say i have the following measures:
TotalSales:=2
Sales last 30 days:=1
so the % over total sales would be 1/2. (50%). I do that calculation, set the measure as % type and it shows me this:
how do i make it show show 50% in 2011?
Friday, December 14, 2012 12:54 PM
Answers

Think this should work for you, but if not, please post your sample data and an image of the model to make things easier on here.
Not Due:
=CALCULATE([Sales] ,ALL(Sales[Age]) ,FILTER(ALL(Sales[Age]) ,Sales[Age] <= 0 ) )
Not Due 91 +:
=CALCULATE([Sales] ,ALL(Sales[Age]) ,FILTER(ALL(Sales[Age]) ,Sales[Age] < 90 ) )
With those, you can simply use them as numerator and denominator in your ratio measure. Ideally, you'd be filtering on a lookup table (dimension), but I just whipped this up as a test. If your model is different, please post a picture.
Here's a link to a detailed description of this pattern on Rob Collie's blog: PowerPivotPro.com
Let me know if that works for you.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com Edited by Brent GreenwoodEditor Monday, December 17, 2012 4:36 PM
 Proposed as answer by Brent GreenwoodEditor Monday, January 21, 2013 11:08 PM
 Marked as answer by Ed Price  MSFTMicrosoft employee Tuesday, September 17, 2013 6:44 AM
Monday, December 17, 2012 4:34 PMAnswerer
All replies

Looks like you're numerator and denominator calc measures aren't filtering properly.
One way to accomplish this would be using the following calcuated measures:
SalesLast90Days:=CALCULATE([Sales],DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),90,DAY)) SalesAllTime:=CALCULATE([Sales] ,DATESBETWEEN('Date'[Date],BLANK(),LASTDATE('Date'[Date]))) SalesLast90%AllTime:= [SalesLast90Days] / [SalesAllTime]
Let me know if that helps.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com
 Edited by Brent GreenwoodEditor Friday, December 14, 2012 4:13 PM
Friday, December 14, 2012 4:12 PMAnswerer 
Hi Brent.
the thing is that im using calculations :
Not dued charges:=COUNTROWS(FILTER(Charges;[Days]<=0))
Not dued 91 days charges:=COUNTROWS(FILTER(Charges;[Days]<90))
So the calculation wold be= [Not dued 91 days charges]/[Not dued charges]
Monday, December 17, 2012 2:57 PM 
Think this should work for you, but if not, please post your sample data and an image of the model to make things easier on here.
Not Due:
=CALCULATE([Sales] ,ALL(Sales[Age]) ,FILTER(ALL(Sales[Age]) ,Sales[Age] <= 0 ) )
Not Due 91 +:
=CALCULATE([Sales] ,ALL(Sales[Age]) ,FILTER(ALL(Sales[Age]) ,Sales[Age] < 90 ) )
With those, you can simply use them as numerator and denominator in your ratio measure. Ideally, you'd be filtering on a lookup table (dimension), but I just whipped this up as a test. If your model is different, please post a picture.
Here's a link to a detailed description of this pattern on Rob Collie's blog: PowerPivotPro.com
Let me know if that works for you.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com Edited by Brent GreenwoodEditor Monday, December 17, 2012 4:36 PM
 Proposed as answer by Brent GreenwoodEditor Monday, January 21, 2013 11:08 PM
 Marked as answer by Ed Price  MSFTMicrosoft employee Tuesday, September 17, 2013 6:44 AM
Monday, December 17, 2012 4:34 PMAnswerer 
Hi Brent,
Just tried your calculations. Let me show what Im trying to do. Its tabular model, so I cant upload excel file.
I tried to :
testNODUED:=CALCULATE(COUNTROWS(Charges);FILTER(Charges;[Dias Vcto Cobro]<=0);Date[PK_Date])
testDUED90:=CALCULATE(COUNTROWS(Charges);FILTER(Charges;[Dias Vcto Cobro]<=90);Date[PK_Date])
testAVG:=CALCULATE([testdue90]/[testnodue];All(Date[PK_Date])) (tried to add allto the filter in the calculate, just in case.)
I tried to add to the test avg a calculate with Date dim. as filter but result seems unproper formed. So it seems like we need a relation between the calculation and the dates. The result its as the first post I posted above.
The fact table is related with a dim.date table.
As you can see when I set it to percent type field it shows 500% (its 5 in integer, it makes no sense as 1/2=0.5. but in the excel query it shows different but also its not the expected. Many thanks!
This shows wrong if I enter the dimdate using a calculate. IF I dont
If I make just [testdue90]/[testnodue] it shows the image in the first post (cant attach more images into this post) I´d like to show 50% on 2011. (1/2)*100=50%
 Edited by the_txeriff Monday, December 17, 2012 5:20 PM
Monday, December 17, 2012 5:18 PM 
I'm confident there is a solution to this problem. Please post some sample data (just a few rows) and an image of the model to make the process efficient.
Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com
Tuesday, December 18, 2012 12:20 AMAnswerer 
Good morning Brent
Seems like it was my fault. By adding ALL(table) using a calculate to the countrows it solved.
the thing is that it was calculating dynamically by current Dued Date (1680) in the "current context" so thats why it was showing strange percentages.
I didnt notice about it. So this looks ok
Tuesday, December 18, 2012 9:37 AM