Correct total sum

[Anonymous]

Hello, 

I am having trouble with a formual its simple just multiplying Total - Actual column by the Target % to give me the Total # per below table in red. The sum however is not working its multiplying the sum of the percentages. 
I have the correct Target column below what its suppose to be.

Can someone help me modify my formula to get the correct sum please?

Target # =

                (SUMX(

                    FILTER(

                            'Data',

                            'Data'[Region] = "Canada"), [Total - Actual])

                            * [Target %]

                            )

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Target # = 
IF(HASONEVALUE('Table'[Date]),[Target %]*[Total - Actual],SUMX('Table',[Target %]*[Total - Actual]))

IF(HASONEVALUE()) can control the output of Total.

Solved: wrong total of measure - Microsoft Power BI Community

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ValtteriN]

Hi,

This is quite common issue. I recommend reading this article to understand what is going on better: Obtaining accurate totals in DAX - SQLBI

In short the table total is calculating the SUMX with empty filter context. Yo ucan create an if logic based on this like this: 

CustomTotal = IF(isblank(SELECTEDVALUE('Data'[Region])),

(SUMX(

                    FILTER(

                            ALL('Data'),

                            'Data'[Region] = "Canada"), [Total - Actual])

                            * [Target %]

                            )

,[Target #])

Here if the row is total (selected value isblank) then we do the SUMX calculation over the entire table otherwise we use the original logic.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Anonymous]

Hi @ValtteriN , this works but it only gives me the total columns how do I get it to give me the numbers and total in the same column please? I wanted it to be like the Correct Target Column in green, please?