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 4 months, 1 week ago.

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

Freedup
Participant
• Started: 6
• Replies: 13
• Total: 19

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

#10911

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

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: 13
• Total: 19

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

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

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

#10920

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

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

This is for Power BI Desktop, correct?

#10921

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

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: 13
• Total: 19

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.