Forum Discussion

mathiasrs's avatar
mathiasrs
Frequent Visitor
2 years ago

Filtered measure based on another measure

Hello there,

Sorry in advance if the question may seem simple, but I am having a hard time solving the problem.

I have created a measure that calculates the inventory turnover ratio as:

 

ITR Operationel = DIVIDE(
    CALCULATE(SUM(LAGPOST[ANTAL]) * -1, FILTER(LAGPOST, LAGPOST[ANTAL] < 0)),
    [AverageInventory],
    1234
)

 

In this code, the cases where ITR = Infinity, I am setting the value of ITR to 1234. 


Subsequently, I have created a measure like this:

 

FilteredITR = 
    AVERAGEX(SUMMARIZE(LAGPOST, LAGPOST[VARENUMMER], "toAverage", [ITR Operationel]), [ITR Operationel])

 

Which calculates the average [ITR Operationel] grouped by itemid (LAGPOST[VARENUMMER]). 

I would, however, like to only calculate the average [ITR Operationel] for itemid's where the [ITR Operationel] <> 1234.

I hope you are able to help me, and once again sorry in advance if the question is poorly described or is easily answered.
Best regards.

Need Help

3 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    mathiasrs,

     

    Try this measure:

     

    FilteredITR =
VAR vBaseTable =
    ADDCOLUMNS ( VALUES ( LAGPOST[VARENUMMER] ), "@toAverage", [ITR Operationel] )
VAR vFinalTable =
    FILTER ( vBaseTable, [@toAverage] <> 1234 )
VAR vResult =
    AVERAGEX ( vFinalTable, [@toAverage] )
RETURN
    vResult
  • mathiasrs's avatar
    mathiasrs
    Frequent Visitor
    2 years ago

    DataInsights 

    I have since made attempts to solve the issue, and your code seems to achieve something similar to what I have managed. However, it seems that the average ITR that is calculated, does not return the correct result. If I create a table measure, and export the data of [ITR Operationel] grouped by my LAGPOST[VARENUMMER] (Item ID), and then calculate the average of all values in this table in excel, I get a different result, from what the DAX measure you wrote returns.

    Let me know if I can add anything to my problem description to enable you to help me more efficiently. 

    Thank you so much in advance.

    • DataInsights's avatar
      DataInsights
      Icon for Super User rankSuper User
      2 years ago

      mathiasrs,

       

      I would need to see your DAX, sample data (table format or pbix link), and expected result.