Find aggregated values and relative values based on a value in a table using lookup

Question

Hello Power BI community. I have a challenge in powerbi. I searched in the forum but I could not find the answer. I have a table of costs of different categories in different years. I want to search categories and find the aggregated costs in each category in each year. But I want to be able for each to display the aggregated cost of previous year(year-1) of each selected category and year.

My output would be:

Cost of Category in year x(the year and the category would be input by user,) Cost of Category.. in previous year (it is year-1).

The table, I have looks like this:

 Year   CostItem 1  CostItem2   Category
 2010   1000         24            A
 2010   1250         3115          A
 2010   455          5115          A
 2010   5848.45      4904          B
 2010   34343        3352          B
 2010   4193         2899          B
 2010   824          3031          B
 2010   3944    3394    B
 2010   1546    3527    C
 2010   2175    1391    C
 2010   4942    2336    C
 2010   2988    5803    C
 2010   2423    3520    C
 2011   4132    2077    A
 2011   1666    834 A
 2011   2043    4419    A
 2011   4307    4030    A
 2011   4303    3339    A
 2011   4989    4619    A
 2011   5977    1655    A
 2011   4338    3698    A
 2011   2734    1321    B
 2011   1662    782     B
 2011   3036    3164    B
 2011   4488    5891    B
 2011   1679    5513    B
 2011   4169    4013    B
 2011   5647    4445    C
 2011   2728    5219    C
 2012   3155    2333    A
 2012   3893    1657    A
 2012   3626    1880    A
 2012   5805    796     A
 2012   753 4137        A
 2012   5537    4230    B
 2012   542 4244        B
 2012   2811    3300    B
 2012   866 3717    C
 2012   2356    2257    C
 2012   2712    2813    C
 2012   2250    4878    C
 2012   796 1610    C
 2012   4297    4241    C
2010    1250    3115    A
 2010   455 5115    A
 2010   5848.45 4904    B
 2010   34343   3352    B
 2010   4193    2899    B
 2010   824 3031    B
 2010   3944    3394    B
 2010   1546    3527    C
 2010   2175    1391    C
 2010   4942    2336    C
 2010   2988    5803    C
 2010   2423    3520    C
 2011   4132    2077    A
 2011   1666    834     A
 2011   2043    4419    A
 2011   4307    4030    A
 2011   4303    3339    A
 2011   4989    4619    A
 2011   5977    1655    A
 2011   4338    3698    A
 2011   2734    1321    B
 2011   1662    782     B
 2011   3036    3164    B
 2011   4488    5891    B
 2011   1679    5513    B
 2011   4169    4013    B
 2011   5647    4445    C
 2011   2728    5219    C
 2012   3155    2333    A
 2012   3893    1657    A
 2012   3626    1880    A
 2012   5805    796     A
 2012   753     4137    A
 2012   5537    4230    B
 2012   542     4244    B
 2012   2811    3300    B
 2012   866     3717    C
 2012   2356    2257    C
 2012   2712    2813    C
 2012   2250    4878    C
 2012   796     1610    C
 2012   4297    4241    C

I tried using lookup function and value functions but could not end up a solution.

Answer 1

You should model your data a bit different, go to query editor, you starting point:

Select the 2 columns CostItem1 and CostItem2. Go to Tab Transform and select: Unpivot, the result is as below:

Now you can use the matrix visual to create 2 levels, first category and then Attributes. End result is overview per year: