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

[Anonymous]

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?

[v-robertq-msft · Accepted solution]

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.

[amitchandak]

@Anonymous , I think you need to create a measure slicer for that.

https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115