Lookup on multiple criteria

Blindreaper · ‎08-14-2017

Hi all,

I am pretty new to Power Bi and DAX and I'm really stuck with a way to get the data I want.

Two things I need to calculate are:

1.Year on Year sales increase/drop.
In the first row I have sales for 2014, store ID 22555. This needs to be compared to store 22555 from 2015.

2. Week by week, Year on Year.

So, 2014 week 30 store 22555 compared with 2015 week 30 store 22555.

Year	Period	Week	Store ID	Store Name	Royalty Sales
2014	7	30	22555	London 1	£2,100.00
2014	7	31	33666	London 2	£3,110.00
2014	8	32	22555	London 1	£3,204.00
2014	8	33	33666	London 2	£5,500.00
2015	7	30	22555	London 1	£1,000.00
2015	7	31	33666	London 2	£2,000.00
2015	8	32	22555	London 1	£3,000.00
2015	8	33	33666	London 2	£2,000.00
2016	7	30	22555	London 1	£5,000.00
2016	7	31	33666	London 2	£2,000.00
2016	8	32	22555	London 1	£500.00
2016	8	33	33666	London 2	£6,000.00
2017	7	30	22555	London 1	£2,000.00
2017	7	31	33666	London 2	£3,000.00
2017	8	32	22555	London 1	£2,200.00
2017	8	33	33666	London 2	£2,500.00

I have tried calculated columns with Lookupvalue on multiple criteria but I am getting error "multiple values returned".

Help would be much appreciated.

Eric_Zhang · ‎08-15-2017

@Blindreaper

For year comparing to year, you can create a measure as below and put it in a line and stacked column chart.

sales increment precentage by year =
VAR salesInPreYear =
    CALCULATE (
        SUM ( Table1[Royalty Sales] ),
        FILTER ( ALLSELECTED ( Table1 ), Table1[Year] = MAX ( Table1[Year] ) - 1 )
    )
RETURN
    DIVIDE ( SUM ( Table1[Royalty Sales] ) - salesInPreYear, salesInPreYear )

For comparing week to previous year's week, you could follow the same pattern of year to year, just add an extra year slicer.

sales increment precentage by yearWeek =
VAR salesInPreYearWeek =
    CALCULATE (
        SUM ( Table1[Royalty Sales] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Week] = MAX ( Table1[Week] )
                && Table1[Year]
                    = MAX ( Table1[Year] ) - 1
        )
    )
RETURN
    DIVIDE (
        SUM ( Table1[Royalty Sales] ) - salesInPreYearWeek,
        salesInPreYearWeek
    )

Blindreaper · ‎08-16-2017

Thank you for your reply @Eric_Zhang

Is there any way to create calculated colums which would show both values in the table?