Adding category on the x-axis for the graph based on measures

[emillia1991]

Dear community,

I want to produce a bar chart in Power BI showing the differences between two years for: sales, average sales, total hours and total training. I want to split it into two categories: Operational (sales and avarage sales) and Training (total hours and total training). Now my bar chart looks like this:

 I want it to be interactive, i.e I want to choose two years in the slicer. The differences have been computed by measures:

E.g. the percentage difference for sales:

D_sales = 100*(DIVIDE(

    [Diff_sales],

    CALCULATE(

        SUM('Sheet1'[sales]),

        FILTER('Sheet1',Sheet1[year]=MIN(

            'Sheet1'[year]))

    )

))

Diff_sales = IF(

    HASONEVALUE('Sheet1'[year]),

    BLANK (),

    CALCUlATE(

        SUM(Sheet1[sales]),

        FILTER('Sheet1','Sheet1'[year]=MAX(Sheet1[year]

        ))

)

        - CALCULATE(

            SUM(Sheet1[sales]),

            FILTER('Sheet1','Sheet1'[year]=MIN(Sheet1[year]

            ))

        )

)

I don't know how to tell Power BI that D_sales belongs to Operational and it should show it on the x-axis. 

[amitchandak · Accepted solution]

@emillia1991 , Use a separate Date or Year Table, joined with your table

new measure =
var _max = maxx(allselected(Date),Date[Year])
var _min = minx(allselected(Date),Date[Year])
return
calculate( sum(Table[Value]), filter('Date', 'Table'[Date] =_max )) - calculate( sum(Table[Value]), filter('Date', 'Table'[Date] =_min ))