cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Count production orders by using a measure as filter

Hi,

I'm trying to calculate the count of production orders which have a deviation percentage above 5%, i.e. count rows of production orders in which a deviation percentage criteria is met.

DeviationPercentage = (sum(Table1[ActualAmount])-sum(Table1[ReqAmount]))/sum(Table1[ReqAmount])

For 5% deviation I have created the following measure where the above measure is used in filter criteria:

DeviationKg5% = COUNTROWS(FILTER(VALUES(Table1[ProductionOrder]),[DeviationPercentage]>=0.05))

Unfortunately when I create a visualization across time (with a Dim_Date) the "aggregated counts" doesn't summarize.

However when I filter on a certain product the "Production Orders across time" visualization works fine.

It's seems my DeviationKg5%  doesn't provide desired results when no filter context is in place. How can I adjust the measure so that when no filters is applied the aggregated results should appear?
1 ACCEPTED SOLUTION
Helper I

I was able to solve this with a summarized temp table and

COUNTROWS(FILTER(temptb, [Deviation percentage]>=0.05
5 REPLIES 5
Helper I

I was able to solve this with a summarized temp table and

COUNTROWS(FILTER(temptb, [Deviation percentage]>=0.05
Super User

@morbu , Try a measure like

Countx(FILTER(VALUES(Table1[ProductionOrder]),[DeviationPercentage]>=0.05),Table1[ProductionOrder])

else

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Helper I

I'm afraid your suggestion gives the same result as outlined

Super User

@morbu ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

check this version too

Countx(FILTER(summarize(Table1[ProductionOrder],"_1",[DeviationPercentage]),[_1]>=0.05),Table1[ProductionOrder])

Helper I

I will try share some sample data/PBIX a bit later

One thing come to mind that might have an impact

Previously I outlined DeviationPercentage as = (sum(Table1[ActualAmount])-sum(Table1[ReqAmount]))/sum(Table1[ReqAmount])

In reality DeviationPercentage = (ActualBlender-ReqBlender)/ReqBlender
where
ActualBlender = calculate(sum(Table1[ActualAmount], Table1[Line]"Blender"
ReqBlender = calculate(sum(Table1[ReqAmount], Table1[Line]"Blender"

Table1 consists of "Blender" and "Press" as values for [Lines]

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors