SUM Issue

[Kasiop]

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

[FataiSanni]

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]

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

[Mikelytics]

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]

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]

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.

[Kasiop]

htanks,  but still not working :

CL SUM2 = SUMX(

'CL',

   CL[Jan] + CL[Feb] + CL[Mar] + CL[Apr] + CL[May]+  CL[Jun]+  CL[Jul]+  CL[Aug]+  CL[Sep]+  CL[Oct]+  CL[Nov]+  CL[Dec]

)

I still have the global total.

Kasiop