Revenue Variance in matrix

[Gareth_Hunt]

Hi Guys new to the forum and my first post.

I would like to calculate the revenue variance for a number of products compared to the sampe period last year.

First Rule  - Revenue Variance is defined as being (CY_avg_rev - PY_avg_rev) x CY_Qty)

Second Rule =ignore if either CY or PY Qty is 0

I have this working at a line level but when I look at the sub total for the group in a matrix 

Revenue Variance = if(CYMeasures[CY-Qty]=0 , blank(),if(PYMeasures[PY-Qty]=0,BLANK(), CYMeasures[CY-AvgREV]-PYMeasures[PY-AvgRev])*CYMeasures[CY-Qty])

its applying the same calculation to the group rather thna suming the line values.

this is what im getting 

	CY_QTY	PY_QTY	CY_REV	PY_REV	CYavgREV	PYavgREV	REV-var
SuB Total A+B+C	150	425	£          65,000	£        180,000	£        433	£           424	£        1,471
Product A	100	350	£          25,000	£          75,000	£        250	£           214	£        3,571
Product B	0	50	£                   -	£          60,000	£            -	£        1,200	£               -
Product C	50	25	£          40,000	£          45,000	£        800	£        1,800	-£     50,000

what I would like to see is

	CY_QTY	PY_QTY	CY_REV	PY_REV	CYavgREV	PYavgREV	REV-var
SuB Total A+B+C	150	425	£          65,000	£        180,000	£        433	£           424	-£     46,429
Product A	100	350	£          25,000	£          75,000	£        250	£           214	£        3,571
Product B	0	50	£                   -	£          60,000	£            -	£        1,200	£               -
Product C	50	25	£          40,000	£          45,000	£        800	£        1,800	-£     50,000

help apprecated 

[parry2k · Accepted solution]

@Gareth_Hunt well the measure will work whatever period you are filtering on, until there is something I don't know about the model.

[v-easonf-msft · Accepted solution]

Hi, @Gareth_Hunt 

Do you have resolved it? If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[parry2k]

@Gareth_Hunt add measure for REV_VAR total

REV-VAR Total = 
SUMX ( VALUES ( Table[Product] ), [Rev-Var Measure] )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Gareth_Hunt]

Hi Parry 2K,

thank you for your quick response -

Im sorry i do not fully understand your expression "values (table[Product])"

How do i create the table referenced [product]  # new to Power BI

many thanks 

Gareth

[parry2k]

@Gareth_Hunt whatever column you are using for product a, b, c , use that in values function.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Gareth_Hunt]

Hi Parry

Really appreciate you helping me -one step closer thats great -still not quite right though

I think it may be the product table is over multiple years  and of course im only interested in a time period im currently controlling using a Month slicer

Many thanks 

Gareth

[parry2k · Accepted solution]

@Gareth_Hunt well the measure will work whatever period you are filtering on, until there is something I don't know about the model.