Count production orders by using a measure as filter

[morbu]

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?

[morbu · Accepted solution]

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

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

[amitchandak]

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

[morbu]

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

[amitchandak]

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

[morbu]

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]