Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Correct total sum

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 multipl...
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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's avatar
ValtteriN
Community Champion
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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?