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

• 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
http://brentgreenwood.blogspot.com

Monday, December 17, 2012 4:34 PM

### 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
http://brentgreenwood.blogspot.com

Friday, December 14, 2012 4:12 PM
• 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
http://brentgreenwood.blogspot.com

Monday, December 17, 2012 4:34 PM
• 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 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.