Forum Discussion

a119526's avatar
a119526
Frequent Visitor
4 years ago
Solved

Subtotal not correct in matrix

Hi All -    I know there are a ton of threads on this topic, but I have not been able to find one that works for me.   See bottom of post for the expected result. I am trying to get the total for...
Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    a119526 try this measure

     

    SUMX(
    SUMMARIZE ( 'SAP Sales Daily', 'SAP Sales Daily'[ABRV_DESC], 'Product'[gpn]),
    VAR volLY = [Volume LY Test]
    VAR volCY = [Volume CY]
    VAR salesLY = [Sales LY test]
    VAR salesCY = [Sales CY]
    VAR priceCY = [Price CY]
    VAR priceLY = [Price LY Test]
    RETURN
    IF ( ( volLY * volCY * salesLY * salesCY ) + 0 <> 0,  volLY * ( priceCY - priceLY ) ) 
)

     

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to 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.⚡

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
4 years ago

a119526 try this measure

 

SUMX(
    SUMMARIZE ( 'SAP Sales Daily', 'SAP Sales Daily'[ABRV_DESC], 'Product'[gpn]),
    VAR volLY = [Volume LY Test]
    VAR volCY = [Volume CY]
    VAR salesLY = [Sales LY test]
    VAR salesCY = [Sales CY]
    VAR priceCY = [Price CY]
    VAR priceLY = [Price LY Test]
    RETURN
    IF ( ( volLY * volCY * salesLY * salesCY ) + 0 <> 0,  volLY * ( priceCY - priceLY ) ) 
)

 

 

✨ Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to 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.⚡

 

a119526's avatar
a119526
Frequent Visitor
4 years ago

You're a legend. Thanks so much