cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

## 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

6 REPLIES 6
Helper I

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

Helper II

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

Hi @Kasiop

`````` 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!

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Helper II

Hi,

I didnt manage to make your formula works...

All my data are in one column called CL:

And here is the powerbi visual:

Helper I

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.

Helper II

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors