Total of table is not correct

[Mike]

Hi, I am having a difficulty to calculate the correct total of table. I use DAX to sum each row for the table, but the total in the card and in the table do not match. The value of the total is not correct, I even don’t know how the number was calculated in the table. But when I check each product, the Missing Opportunities is correct.

Here is my sample file, could you please help?

Thanks!

Mike

Opportunity_test.pbix (95.3 KB)

[BrianJ]

@Mike,

I recently did a video on fixing card values that I think will give you everything you need to solve this one, in terms of getting accurate values on both the card totals and the table totals.

If you still have problems after working through the video, just give a shout.

– Brian

Fixing Card Visual Results In Power BI

[Harsh]

Hello @Mike,

Thank You for posting your query onto the Forum.

The total showcased into the Card Visual as well as in the Table Visual are incorrect. Upon checking the totals in my calculator it’s actually $9,987.15/-. As our @BrianJ has already provided a link of a great video which showcases how to fix the totals. Here’s the formula accompanied with the screenshot of the final results provided for the reference -

Missing Opportunities - Total = 
SUMX(
    SUMMARIZE(
        D_Product_tbl , 
        D_Product_tbl[Produc_ID] ,
        "@Total" , 
        [Missing Opportunities] ) , 
    [@Total]
)

I’m also attaching the working of the PBIX file for the reference as well as providing a link of the post which one of our expert member @Greg had created which specifically addresses this type of issue.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Opportunity_test - Harsh.pbix (95.7 KB)

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[Mike]

Hi, @Harsh and @BrianJ,

Thanks both for helping me understand the problem. You guys are awesome!

@BrianJ, I really enjoyed your video, which not only provided the solution, but also explained the reasons. During your demo, you showed the external tools you added to your PowerBI desktop. Could you please share how did you add those tools to your PowerBI? if you have a video about the external tool, that will be great!

Thanks again!

Mike

[BrianJ]

@Mike,

Great- glad that was helpful.

With regards to External Tools, you’ve come to the right place - it’s probably my favorite feature in Power BI, and I have the following info for you:

How to Trick Out Your External Tools Menu:

Level up Your External Tools Menu in Power BI

Preview of the External Tool that Will Change the Way You Do Power BI:

Tabular Editor 3 Will Change The Way You Power BI

Video on Practice Dataset Tool Developed by @samaguire, @Melissa and me:

How To Install Practice Dataset Power BI Tool

Section of the Forum with Links to Tools Created by Me and Other Members:

Software & Tools

Finally, if you go to the current Data Challenge 13, @haroonali1000 has info on an external tool called DataMarc Documenter, which is a great tool for automating documentation of your PBI reports.

Enjoy!

• Brian

[Mike]

@BrianJ, thanks a lot! The tool you demonstrated that can see the virtual table is fantastic! I really love it. I will explore those tools and I am pretty sure they will be very helpful!

Thanks!

Mike