Home Forums Power Pivot Getting different results with VAR in PP

This topic contains 5 replies, has 2 voices, and was last updated by  Freedup 2 months ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #10908

    Freedup
    Participant
    • Started: 6
    • Replies: 12
    • Total: 18

    I was just getting ready to send in a blog piece on my journey with VAR and found an inconsistency I can’t really explain…

    Below are the two measures, one returns correct results and other does not.  Please advise.  One of my Variables is using a measure which is the result of a calculation (Avg Lines), which the other one is a DistinctCount measure (Unique items ordered).  The measure using the Avg. Line VAR returns the correct value, while the other does not.  The only difference in these measures is the Metric Var statement.

    mediantest1 = VAR Metric =  [AvgOrderlines]   This one returns results as expected

    mediantest1 = VAR Metric = [Unique Items Ordered]  This one returns a count of all items ordered instead of the median.

    Here is full measure:

    Median test = VAR Metric = [Unique Items Ordered]

    VAR Population =Values(vd_Customer[Customer Name])

    VAR FactTable = vf_SALESDETAIL

    Var MedianTest1 =

    (

    MINX (

    TOPN (

    COUNTROWS ( CALCULATETABLE (  Population , FactTable ) ) / 2,

    CALCULATETABLE ( Population ,Facttable ),

    metric, 0

    ),

    metric

    )

    + MINX (

    TOPN (

    ( COUNTROWS ( CALCULATETABLE (  Population , FactTable ) ) + 1 )

    / 2,

    CALCULATETABLE ( population ,Facttable ),

    metric, 0

    ),

    metric

    )

    )

    / 2

    Return

    MedianTest1

     

    Thanks for your help.

     

     

    #10911

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    The issue is most likely context transition (not happening when you need it to occur).

    What are the formulas for [Unique Items Ordered] and [AvgOrderlines]?

    #10916

    Freedup
    Participant
    • Started: 6
    • Replies: 12
    • Total: 18

    Average Line items = divide([ordered line items],[order hits])

    orderedlineitems = count(vf_salesdetail[link to txnid)

    Order Hits = Calculate(DISTINCTCOUNT(vf_SALESDETAIL[LinkToTxnID]),vdf_SALES_DOC_ATTRIB[SalesTxn Document Type] = “Invoice”)

    unique items ordered = distinctcount(vf_salesdetail[link to itemid]

    Thanks for your help. Fran

    #10920

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    OK. [Unique Items Ordered] should have no problem with context transition.

    This is for Power BI Desktop, correct?

    #10921

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Fran,

    If you were to express you measure in a sentence, would you say the desired median is the median item order quantity across all of a customer’s orders (within the current filter context) or are you trying to calculate a different median?

    #10922

    Freedup
    Participant
    • Started: 6
    • Replies: 12
    • Total: 18

    Yes you are right.. I am  wanting the Median for all customer orders for year (which is a slicer). Then   rank the individual customer against the median.    (if customer A’s item orders are 1.5 times greater then the median, it is ranked “A”, etc. )    The  “total” of the mediantest measure is really what I want for the ranking.     Actually, I have one more measure I use to get the actual median… I just didn’t know how to combine it with the long mediantest measure.

    Its   mediantesttotal = calculate([mediantest],all(customer)).   This is what I actually use for the ranking measure.

    Using VAR with the other Median and Ranking measures works great.  it just seems to be this one measure.

    Fran

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.