Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Gareth_Hunt
Helper II
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_QTYPY_QTY CY_REV  PY_REV  CYavgREV  PYavgREV  REV-var 
SuB Total A+B+C150425 £          65,000 £        180,000 £        433 £           424 £        1,471
Product A100350 £          25,000 £          75,000 £        250 £           214 £        3,571
Product B050 £                   -   £          60,000 £            -   £        1,200 £               -  
Product C5025 £          40,000 £          45,000 £        800 £        1,800-£     50,000

 

what I would like to see is

 CY_QTYPY_QTY CY_REV  PY_REV  CYavgREV  PYavgREV  REV-var 
SuB Total A+B+C150425 £          65,000 £        180,000 £        433 £           424-£     46,429
Product A100350 £          25,000 £          75,000 £        250 £           214 £        3,571
Product B050 £                   -   £          60,000 £            -   £        1,200 £               -  
Product C5025 £          40,000 £          45,000 £        800 £        1,800-£     50,000

 

help apprecated 

2 ACCEPTED SOLUTIONS

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

View solution in original post

v-easonf-msft
Community Support
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

View solution in original post

6 REPLIES 6
v-easonf-msft
Community Support
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

parry2k
Super User
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.

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

 

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

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.