Forum Discussion

Kasiop's avatar
Kasiop
Helper II
3 years ago

SUM Issue

Dear Community, 

 

I have a simple issue using "sum"... 

I have a table with multiple lines, linked an id/category but when I try to have the total cost per category, here which is supposed to be 300 for the category "product", Power bi is showing the total cost so 600.  I have added the grey column just to let you the result shown by powerbi:

I have tried to create a SUMX(Cost, cost(jan) + cost(feb)+ ...) but I end up with the same result.

 

If any one have a simple trick, that will be great.

 

thanks,

Kasiop

Need Help

6 Replies

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    Hi Kasiop 

     

    Please try the following

     

     SUMX(
   VALUES(Cost[Type]), 
   cost(jan) + cost(feb)+ ...) 
)

     

    If this does not help please provide a piucture of your data model with relevant columns as well as how your current result looks in Power BI.

     

    Best regards

    Michael

    -----------------------------------------------------

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

    @ me in replies or I'll lose your thread.

    -----------------------------------------------------

    LinkedIn

    • Kasiop's avatar
      Kasiop
      Helper II
      3 years ago

      Hi, 

      I didnt manage to make your formula works...

      All my data are in one column called CL: 

       

      And here is the powerbi visual:

       

       

      • FataiSanni's avatar
        FataiSanni
        Advocate III
        3 years ago

        Hi,

        I believe this should work, based on the table you posted.

         

         SUMX(
   'CL', 
   Jan + Feb + Mar + Apr +...
)

         

        Alternatively, I will advise you to unpivot the month columns and have all Months in a single column and values in another column. That way, SUM(Values) will just work.

  • FataiSanni's avatar
    FataiSanni
    Advocate III
    3 years ago

    Hi,

     

    I'm assuming you are creating a measure, try this instead 

     

    Total Cost = sum('Table'[Cost Jan]) + sum('Table'[Cost Feb])

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

    • Kasiop's avatar
      Kasiop
      Helper II
      3 years ago

      Thanks a lot, I tried to implement your solution but I am not sure what I am doing wrong but the formula still show me the "wrong" total cost:

      CL SUM = SUM('CL'[Jan])+ SUM ('CL'[Feb])+ SUM ('CL'[Mar])+ SUM ('CL'[Apr])+ SUM ('CL'[May])+ SUM ('CL'[Jun])+ SUM ('CL'[Jul])+ SUM ('CL'[Jul])+ SUM ('CL'[Aug])+ SUM ('CL'[Sep])+ SUM ('CL'[Oct])+ SUM ('CL'[Nov])+ SUM ('CL'[Dec]).