Solved: Subtotal not correct in matrix

a119526 · ‎10-01-2021

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

parry2k · ‎10-01-2021

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

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

parry2k · ‎10-01-2021

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

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.

a119526 · ‎10-01-2021

You're a legend. Thanks so much

parry2k · ‎10-01-2021

@a119526 very hard to figure out from image, drop a sample pbix file to look into it.

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.

a119526 · ‎10-01-2021

Sorry about that @parry2k .

I tried dropping the file at the link below. Not sure if there is a better way to share .pbix files? I couldn't upload to the community forum directly. Thank you for your help.

The visualization I am trying to fix is on the PVM Product Tab.

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