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 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 %]
                            )

 

 

 

3 Replies

  • 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?

       

  • v-zhangti's avatar
    v-zhangti
    Community Support
    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.