Calculating variances and divisions

[andresf]

Hi, can someone please help with this?

I have this raw data set

PRODUCT	SALES ($)	QUANTITY (KG)	DATE	YEAR	QUARTER	NET PROFIT
Paper				2020	q2
Metal					q1
Paper				2020	q2
Metal				2021	q1
Paper				2021	q2
Metal				2021	q2

In Power BI, how can I get a matrix/table visualization like this:

|	2021	2021	2020	2020	VARIANCE		PROFIT MARGIN
PRODUCT	SALES	NET PROFIT	SALES	NET PROFIT	SALES	NET PROFIT	2021	2020
Paper							=net profit (paper 2021)/sales (paper 2021)
Metal

Thanks!

Andres

 

[Jeffrey Mahoney]

How about a simple Excel table and some formulas?

Book2
	A	B	C	D	E
1		2020	2020	2021	2021
2	Product	Sales	Net Profit	Sales	Net Profit
3	Paper	666	600	602	550
4	Metal	221	200	1787	1600
Sheet5

Cell Formulas
Range	Formula
A3:A4	A3	=UNIQUE(Table1[PRODUCT])
B3:B4,D3:D4	B3	=SUMIFS(Table1[SALES ($)],Table1[PRODUCT],Sheet5!$A3,Table1[YEAR],Sheet5!B$1)
C3:C4,E3:E4	C3	=SUMIFS(Table1[NET PROFIT],Table1[PRODUCT],Sheet5!$A3,Table1[YEAR],Sheet5!C$1)
Dynamic array formulas.

 

[Jeffrey Mahoney]

I turned your data into a table called Table1. The Unique formula in Cell A3 spills out into as many rows as the table has.