Calculate based on category or year

DAX DAX Calculations
1

Hi community.
Please I am new to power bi and I am trying to solve a problem, I hope you can help.

I am trying to achieve a column that calculate based on the year.
e.g., if year = 2018, then sum all military budget for 2018 and divide it by the sum of # of miliary trainings. This should give same column for the expect result column for each year. Image below show the table.

Thanks if you can help on the formula.

image
image736×399 11.6 KB

2

Hi,

please try this measure:

avg budget for training = 

var _costsperyear = 

    CALCULATE(
        SUMX('Table','Table'[Military training budget]),ALLEXCEPT('Table','Table'[Year]))

var _trainingsperyear = 

    CALCULATE(
        SUMX('Table','Table'[# of military trainings]),ALLEXCEPT('Table','Table'[Year]))

return

DIVIDE(_costsperyear,_trainingsperyear,0)
  • Sabine
1 Like
3

Hi Sabine,
Thanks alot, worked like magic! I am grateful.
Dayo

1 Like