exceldaxpowerpivot

Excel DAX: Dynamically calculate the countof rows where each row's value is compared to the min value


My data contains a list of policies and each company's premium. I would like to calculate the count of policies where each company's premium per policy equals the minimum premium per policy. This measure should also be able to dynamically recalculate when user deselects a company using a filter/slicer. For example, if all companies are selected, then TG has a count of 1, IF a count of 3, and CW a count of 1, however if IF is filtered out, then TG should now have a count of 4 while CW still with a count of 1.

enter image description here


Solution

  • Try this approach:

    My count:=
      COUNTX(
        DISTINCT('YourTable'[PolicyNumber]),
        (
          var thisPreimum = CALCULATE(MIN([Premium]))
          var allPremium = CALCULATE(MIN([Premium]), ALLSELECTED('YourTable'[Company]))
          return IF(thisPreimum = allPremium, 1)
        )
      )
    

    enter image description here

    enter image description here