Search code examples
powerbidaxpowerquerypowerbi-desktopdaxstudio

Quick calculation where numerator depends on filters but denominator is fixed (DAX)

I have a table in PowerBI called "Dati Popolazione ATTR", which is something like:

Region | Province | Town | Population | Males | Females | Attribute
R1     | P1       | T1   | 1000       | 500   | 500     | A1
R1     | P1       | T1   | 1000       | 500   | 500     | A2
R1     | P1       | T1   | 1000       | 500   | 500     | A3
R2     | P2       | T2   | 2000       | 600   | 1400    | A1
R2     | P2       | T2   | 2000       | 600   | 1400    | A2
R2     | P2       | T2   | 2000       | 600   | 1400    | A3
R3     | P3       | T3   | 1500       | 550   | 950     | A1
R3     | P3       | T3   | 1500       | 550   | 950     | A2
R3     | P3       | T3   | 1500       | 550   | 950     | A3

I want to create a quick measure called 'Affinity'. This should have the following calculation:

Affinity = sum of the selected attribute / sum of the selected attribute in absolute terms regardless of any filter

Denominator should not vary if I select any filter.

Can you help me?

Solution

  • To achieve your aim we need to use two powerful functions in DAX: ALL() and DIVIDE().

    This code divides the current sum of filtered populations by the sum of all populations.

      Affinity=DIVIDE( 
  SUM('Dati Popolazione ATTR'[Population]),
  SUMX(All('Dati Popolazione ATTR'),'Dati Popolazione ATTR'[Population])
  )