Forum Discussion

morbu's avatar
morbu
Icon for Helper I rankHelper I
5 years ago
Solved

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?
Need Help
  • morbu's avatar
    morbu
    5 years ago

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

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      morbu
      Icon for Helper I rankHelper I
      5 years ago

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

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        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's avatar
    morbu
    Icon for Helper I rankHelper I
    5 years ago

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

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