Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Need help with finding the minimum value for a measure

I am trying to find the minimum value for my Measure 3 which is addition of two other measures. Measure 1 and 2. I am using this formula for the minimum value: MINX(Table,Measure3)

The data granularity of Measure 1 and Measure 2 is as shown in the image. Measure 1 is as of today and doesnt change wrt date. Measure 2 changes with date.

Now when I add Measure 3 in the same row as category and product, it shows the correct value for measure 3 even after filtering for data range. When I am taking min of measure 3 however which belongs to a separate scorecard visual, the date filer applies even to the products and those products which are not a part of the filtered date range (courtesy of the product-product relationship between 2 tables), the min gets the minimum value for the filtered products. I want it to take minimum value of all products whether or not it is within the date range. I added all(Date) but this does not capture the change happening in Measure 2 due to date and that is incorrect too. Measure 2 will change with date. Measure 1 wont. I basically want the Minimum of all the products whether or not they are in the filtered date range.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

    The pattern is:
    MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    etc.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hello, 

       

      Appreciate the help. However, this did not work for me. My issue is that the date slicer filters out some products. I want minimum value of those products too.