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

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

Reply
morbu
Helper I
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.
morbu_2-1619607178881.png

 


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

 morbu_3-1619607209284.png

 

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
morbu
Helper I
Helper I

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

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

View solution in original post

5 REPLIES 5
morbu
Helper I
Helper I

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

COUNTROWS(FILTER(temptb, [Deviation percentage]>=0.05
amitchandak
Super User
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.

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

@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])

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]

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.