Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## 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.

3 REPLIES 3
Frequent Visitor

@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.

Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

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``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors