Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am having a table as below
Year | Designation | Salary |
2010 | Engineer | 50000 |
2010 | Sr.Engineer | 70000 |
2010 | A.Manager | 90000 |
2011 | Engineer | 55000 |
2011 | Sr.Engineer | 75000 |
2011 | A.Manager | 95000 |
2012 | Engineer | 65000 |
2012 | Sr.Engineer | 85000 |
2012 | A.Manager | 105000 |
In the table I am able to filter the records based on Designation (using Visual Level Filter option in Power BI)
For example I am filtered the designation as 'Enginner'
Year | Designation | Salary |
2010 | Engineer | 50000 |
2011 | Engineer | 55000 |
2012 | Engineer | 65000 |
Now I need to derive the result as below
Year | Designation | Salary | Sum |
2010 | Engineer | 50000 | 5000 |
2011 | Engineer | 55000 | 105000 |
2012 | Engineer | 65000 | 170000 |
I need to derive the SUM by adding the Current and Previous years salary. Kindly help me to get the above result table.
Thanks,
Corrected years.
Solved! Go to Solution.
Hi, try witt this DAX
Salary running total in Year = CALCULATE ( SUM ( 'Table1'[Salary] ), FILTER ( ALLEXCEPT ( Table1, Table1[Designation] ), Table1[Year] <= MAX ( Table1[Year] ) ) )
Hi,
@Anonymous, I agree with @Vvelarde that madan27 needs to calculate the SUM by adding the Current and all Previous years' salary(not just the last previous year).
@madan27, the formula provided @Vvelarde above should work in your scenario. Just replace "Table1" with your real table name, and use the formula to create a new measure, then show it on the Table visual with other corresponding columns.
Regards
Hi, try witt this DAX
Salary running total in Year = CALCULATE ( SUM ( 'Table1'[Salary] ), FILTER ( ALLEXCEPT ( Table1, Table1[Designation] ), Table1[Year] <= MAX ( Table1[Year] ) ) )
@Vvelarde That is going to give a running total of ALL years. I read the requirement as current year, plus 1 year ago? I could be nuts, but if not...
Maybe something like... ?
Salary CY :=
CALCULATE (
SUM ( Table1[Salary] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Designation] ),
Table1[Year] = MAX ( Table1[Year] )
)
)
Salary PY :=
CALCULATE (
SUM ( Table1[Salary] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Designation] ),
Table1[Year] = MAX ( Table1[Year] ) - 1
)
)
Salary CY+PY := [Salary CY] + [Salary PY]
Hi @madan27
In the expected output you have two Engineer records for 2011. Should one of these be for 2012?
Yes Phil, your correct. Sorry for the typo....