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 the Impact(Price) column to be a summation of the lines above. 

 

I think the issue has to do with the seperate product table that I'm using, but I don't know why or how to address. Any input would be super appreciated! 

 

The DAX for the measure is seen below. 

 

Annual Impact (Price) Selected Test = 
VAR PVMProduct = 
FILTER(
    'Product', 
    [Volume LY Test] *[Volume CY]*[Sales LY test]*[Sales CY]+0<>0
)
RETURN
CALCULATE(SUMX(
    PVMProduct, 
    [Volume LY Test]*([Price CY]-[Price LY Test])))

 

I tried creating this additional measure, but it returns the same total as the measure above.

 

Annual Impact (Price) Selected Test1 = 
VAR _table = 
    SUMMARIZE('Product','Product'[gpn],"Price Impact123",[Annual Impact (Price) Selected Test])
RETURN
    If(HASONEVALUE('Product'[gpn]),[Annual Impact (Price) Selected Test],SUMX(_table,[Price Impact123]))

 

Here is the ouput showing the incorrect total.

Below is the relationship in the report. 

Edit: adding link to .pbix

https://drive.google.com/file/d/1b2RFHHSrPq2WZGr_NpN3VxznEUT_ngvy/view?usp=sharing 

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

     

4 Replies

  • parry2k's avatar
    parry2k
    Super 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.⚡