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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
A_a_a
Helper III
Helper III

Incorrect Total in Matrix Table

Hi All,

 

I know that this topic was discussed many times, but still I cannot get the correct Totals...

 

Subtotals are correct, but Totals are not...

 

Please see my calculations.

The matrix table below shows products B0 and B1 and weeks, when I want to sum up weeks’ values for one product the subtotals are correct, but totals aren't... -3181-2116 = -5297.

 

A_a_a_1-1691355022493.png

 

Please see my measures:

Measure 2 =

IF(COUNTROWS(VALUES(Weeks[Week]))=1,[Calculations], IF(COUNTROWS(VALUES(dim_Product[Product]))=1,sumx(VALUES(Weeks[Week]),[Calculations]),

sumx(VALUES(dim_Product[Product]),[Calculations])))

Calculations = DIVIDE([A],[C],0)-DIVIDE([B],[C],0)

 

Please help.

G.

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@A_a_a your measure should be just this:

 

Measure 0 = 
VAR __result = 

        SUMX (
            SUMMARIZE (
                'Actual Table',
                dim_product[Product],
                dim_date[Week],
                "@Cal", [Calculations]
            ),
            [@Cal]
        )

RETURN
__result

 

👉 Learn Power BI and Fabric - subscribe to our YT channel - @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! ❤️



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

10 REPLIES 10
parry2k
Super User
Super User

@A_a_a your measure should be just this:

 

Measure 0 = 
VAR __result = 

        SUMX (
            SUMMARIZE (
                'Actual Table',
                dim_product[Product],
                dim_date[Week],
                "@Cal", [Calculations]
            ),
            [@Cal]
        )

RETURN
__result

 

👉 Learn Power BI and Fabric - subscribe to our YT channel - @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! ❤️



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.

@parry2k Thank you! Total values are correct now!

parry2k
Super User
Super User

@A_a_a share the file using one drive/google drive link.



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.

parry2k
Super User
Super User

@A_a_a it is hard to tell what is going on without a data model, and definition of measures. I would recommend sharing pbix file, and removing sensitive information before sharing.

 

 



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

 

Thank you.

I created a new model to share using the same logic and putting your measure.

I noticed that your measure works perfectly when there is no filter applied:

 

 

A_a_a_2-1691493930345.png

but when I choose any week, Total is incorrect:

A_a_a_3-1691494014062.png

How can we change the measure to be correct when we want to choose/filter Week or Product in Slicers? 

I wanted to attached the file, however I cannot see such option here 😕 

 

Thanks,

G.

 

parry2k
Super User
Super User

@A_a_a whatever your transaction table is on which you are writing the measure.

 

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo



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

 

Thank you for your message.

This is what I thought, but again I have an incorrect figure in Totals. 

 

I am wondering that maybe it is due to the Calculation measure, which is based on two different tables.

 

Calculations = DIVIDE([A],[C],0)-DIVIDE([B],[C],0)

 

 [A] and [C] are taken from one table and [B] from another one.

 

Please let me know what you think.

 

G.

parry2k
Super User
Super User

@A_a_a I think you need this:

 

Measure 2 =

IF(COUNTROWS(VALUES(Weeks[Week]))=1,[Calculations], IF(COUNTROWS(VALUES(dim_Product[Product]))=1,sumx(VALUES(Weeks[Week]),[Calculations]),
sumx(SUMMARIZE(YourTable, dim_Product[Product], Weeks[Week], "@Cal", [Calculations]), [@Cal] ) )

 

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo



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

 

Thank you for your help.

Just to clarify: SUMMARIZE(Your table... so which table...? 

 

Thanks.

G.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors