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
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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.