Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

ALLEXCEPT MEDIAN

Hello,

 

I have pricing data for a list of items that are sold across many locations. The locations are split into two distinct groups which price the same items very differently. I want to find the median price per item across locations and bucketed by which group they belong to. I was able to achieve this initial part using this formula:

 

MP = CALCULATE(MEDIANX(FactTable,FactTable[Price]),ALLEXCEPT(FactTable,FactTable[Item],DimDetails[Group]))
 
What I would like to do is add a filter to the formula so that it only looks at price data for the last month, but I'm not sure how to structure the formula using ALLEXCEPT.
 
Any help would be great. Thanks
 
Any help would be great, thanks
Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    try

    MP = CALCULATE(MEDIANX(FactTable,FactTable[Price]),ALLEXCEPT(FactTable,FactTable[Item],DimDetails[Group]),FactTable[Price Date]>EOMONTH(TODAY(),-1))

    do not hesitate to give a kudo to useful posts and mark solutions as solution

1 Reply

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    try

    MP = CALCULATE(MEDIANX(FactTable,FactTable[Price]),ALLEXCEPT(FactTable,FactTable[Item],DimDetails[Group]),FactTable[Price Date]>EOMONTH(TODAY(),-1))

    do not hesitate to give a kudo to useful posts and mark solutions as solution