Power BI DAX Sum By Year

1
votes

Very new to Power BI and DAX and would appreciate a push in the right direction with this, seemingly simple, scenario, please. I have the following dataset:

Job Name    Quarter Year    Cost
alpha       1   2019    210
alpha       2   2019    100
alpha       3   2019    90
alpha       4   2019    28
beta        1   2020    100
kappa       1   2019    100
kappa       2   2019    90
beta        2   2020    100
beta        3   2020    75
beta        4   2020    30
kappa       3   2019    10
kappa       4   2019    30

All I am trying to do is get a measure/calculated column which calculates the total [Cost] per [Job Name] for each year. So for example I would get, for alpha, the value: 428. For kappa it would be: 230. Thanks!

2
powerbidax

2 Answers

3
votes

Use the below DAX to obtain the above result: New Table :

SUMMARIZE('Table','Table'[Job Name],"New Column",SUM('Table'[Cost]))

1
votes

Using SUMMARIZE (as @Shilpa suggests) will return/add a new table to your report data source. I think this is not your requirement.

As you are looking for a solution using a measure or a calculated column, lets know a bit details about them as they are not same with the functionality-

Calculated columns:

  • Evaluated for each row in your table, immediately after you hit 'Enter' to complete the formula
  • Calculate new values from existing values for each specific rows. For example, if you have value 5 in column "A" and 4 in column "B", you can create a new Calculated column C as (A x B) which will store the result 20. This will generate results in all rows using the calculation (A x B).
  • Result in Calculated column saved to the model as like other column's value.

Measures:

  • Evaluated when you use it in a visual and the visual is rendered
  • Measure always holds the aggregated value like - SUM, AVERAGE, COUNT.
  • Not saved anywhere (well, actually there's a cache in the report layer but it's not part of the file when you hit Save)

Now, for your scenario/requirement I think you need a simple measure as below-

total_cost = SUM('your_table'[Cost])

Your measure is ready now. Just pull column "Job Name" and measure "total_cost" to you visual. You will get your expected output. You can use slicer to check your value in/for different dimension. Just play around :)