Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create a quick calculation with a part fixed and another dependent on the filters (PowerBI - DAX)

So 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 population) / (sum of the selected attribute in absolute terms regardless of any filter / 60483973).

Can you help me?

 

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, Anonymous 

    According to your description, you can try this measure:

    Affinity =

var _sumofselected=SUMX(ALLSELECTED('Dati Popolazione ATTR'),[Population])

var _sumofall=SUMX(ALL('Dati Popolazione ATTR'),[Population])

var _divide1=DIVIDE(_sumofselected,_sumofall)

var _divide2=DIVIDE(ABS(_sumofselected),60483973)

return DIVIDE(_divide1,_divide2)

     

    Then you can create a card chart to place the measure and a Slicer to place the [Attribute], like this:

     

    And you can get what you want.

    You can download my test pbix file here

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies