locked
% over total wrong calc? RRS feed

  • 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


    Monday, December 17, 2012 4:34 PM
    Answerer

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



    Friday, December 14, 2012 4:12 PM
    Answerer
  • 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


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