cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Revenue Variance in matrix

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

2 ACCEPTED SOLUTIONS
Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Community Support

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

6 REPLIES 6
Community Support

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

Super User

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper II

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

Super User

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper II

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

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.