Replicating Excel formulas in Power BI to get Variances

[Anonymous]

Hi all - I have a table from Excel we'd like to recreate in PBI, but some of the columns are based on calculations from other columns, and I cant seem to get the DAX right for this - can anyone help?

Below is a screen shot of the Excel table I'm looking to make the YOY part, its basically the 2021 figure divided by the corresponding 2019 figure minus 1 to get the difference ie SUM=(B1/G1-1). I've been able to make some simple measures to get the PM% in PBI but the other part is eluding me

All the data is in a tabular format, the column headings are as displayed

How would I get the YOY variance for Product 1?

Thanks in advance for any help! 

[Icey · Accepted solution]

Hi @Anonymous ,

Assuming only two years(2019 & 2021) of data, please try this:

Net Sales Measure =
VAR NetSales_ =
    SUM ( 'Table'[Net Sales] )
VAR NetSales_2019 =
    CALCULATE ( SUM ( 'Table'[Net Sales] ), 'Table'[Year] = 2019 )
VAR NetSales_2021 =
    CALCULATE ( SUM ( 'Table'[Net Sales] ), 'Table'[Year] = 2021 )
RETURN
    IF (
        ISFILTERED ( 'Table'[Year] ),
        NetSales_,
        FORMAT ( DIVIDE ( NetSales_2021, NetSales_2019 ) - 1, "##%" )
    )

Product Margin Measure =
VAR ProductMargin_ =
    SUM ( 'Table'[Product Margin] )
VAR ProductMargin_2019 =
    CALCULATE ( SUM ( 'Table'[Product Margin] ), 'Table'[Year] = 2019 )
VAR ProductMargin_2021 =
    CALCULATE ( SUM ( 'Table'[Product Margin] ), 'Table'[Year] = 2021 )
RETURN
    IF (
        ISFILTERED ( 'Table'[Year] ),
        ProductMargin_,
        FORMAT ( DIVIDE ( ProductMargin_2021, ProductMargin_2019 ) - 1, "##%" )
    )

PM% =
VAR PM_ =
    DIVIDE ( SUM ( 'Table'[Product Margin] ), SUM ( 'Table'[Net Sales] ) )
VAR PM_2019 =
    CALCULATE (
        DIVIDE ( SUM ( 'Table'[Product Margin] ), SUM ( 'Table'[Net Sales] ) ),
        'Table'[Year] = 2019
    )
VAR PM_2021 =
    CALCULATE (
        DIVIDE ( SUM ( 'Table'[Product Margin] ), SUM ( 'Table'[Net Sales] ) ),
        'Table'[Year] = 2021
    )
RETURN
    IF ( ISFILTERED ( 'Table'[Year] ), PM_, PM_2021 - PM_2019 )

Gross Profit Measure =
VAR GrossProfit_ =
    SUM ( 'Table'[Gross Profit] )
VAR GrossProfit_2019 =
    CALCULATE ( SUM ( 'Table'[Gross Profit] ), 'Table'[Year] = 2019 )
VAR GrossProfit_2021 =
    CALCULATE ( SUM ( 'Table'[Gross Profit] ), 'Table'[Year] = 2021 )
RETURN
    IF (
        ISFILTERED ( 'Table'[Year] ),
        GrossProfit_,
        FORMAT ( DIVIDE ( GrossProfit_2021, GrossProfit_2019 ) - 1, "##%" )
    )

GP% =
VAR GP_ =
    DIVIDE ( SUM ( 'Table'[Gross Profit] ), SUM ( 'Table'[Net Sales] ) )
VAR GP_2019 =
    CALCULATE (
        DIVIDE ( SUM ( 'Table'[Gross Profit] ), SUM ( 'Table'[Net Sales] ) ),
        'Table'[Year] = 2019
    )
VAR GP_2021 =
    CALCULATE (
        DIVIDE ( SUM ( 'Table'[Gross Profit] ), SUM ( 'Table'[Net Sales] ) ),
        'Table'[Year] = 2021
    )
RETURN
    IF ( ISFILTERED ( 'Table'[Year] ), GP_, GP_2021 - GP_2019 )

If your data structure is not the same as mine, it may need to use "Pivot and UnPivot" to transform it.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Does that data in this format? Or you have the years in a row.  Better to have format were year is on rows

With years on rows

Create a new Year/Date table and then try measure like 

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

[Anonymous]

Thanks for the input - it doesnt all have to be in one visualisation so I guess I can have a table to the side doing the YOY Variance, I do need it to be comparable by product line tho, would the 4 lines you've provided be 4 seperate Measures?