October 7, 2018 at 11:25 pm #10908
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 =
COUNTROWS ( CALCULATETABLE ( Population , FactTable ) ) / 2,
CALCULATETABLE ( Population ,Facttable ),
+ MINX (
( COUNTROWS ( CALCULATETABLE ( Population , FactTable ) ) + 1 )
CALCULATETABLE ( population ,Facttable ),
Thanks for your help.October 8, 2018 at 2:28 pm #10911
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]?October 8, 2018 at 8:00 pm #10916
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. FranOctober 9, 2018 at 1:49 am #10920
OK. [Unique Items Ordered] should have no problem with context transition.
This is for Power BI Desktop, correct?October 9, 2018 at 2:37 am #10921
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?October 9, 2018 at 3:51 am #10922
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.
You must be logged in to reply to this topic.