ALLexcept help to calculate correct total

[farmboy]

HI

trying to calculate my total so that the calculation creates a consistent calculation.

i.e. the total should be 955 for actual and 900 for forecast. I understand i need to use all except in my filter but have not managed to get this to work.power bi.pbix (98.6 KB)

all help appreciated

[Harsh]

Hello @farmboy,

Thank You for posting your query onto the Forum.

Well for actuals you already have a correct total i.e. 955 but still just write a simple measure rather than using naked column from the table and to achieve the correct totals of forecast i.e. 900. Just write one small measure to fix it. Below are the measures provided for the reference -

actuals = SUM( ACTUAL[actual] )

fcst totals = 
SUMX(
    SUMMARIZE(
        FarmDetails , 
        FarmDetails[Company] , 
        "@Totals" , 
        [fcst] ) , 
    [@Totals]
)

Also one of our expert @Greg had also created a post pertaining to the topic about “How To Fix Incorrect Totals”. I’m providing a link below of that which contains all the posts that were created by the members so far pertaining to this very similar topic and also contains all the links of the videos as well.

I’m also attaching the working of the PBIX file for the reference as well.

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

Thanks and Warm Regards,
Harsh

power bi.pbix (99.2 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…

[EnterpriseDNA]

Hi @farmboy, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @Farmboy, we’ve noticed that no response has been received from you since the 21st of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @farmboy, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!