Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
wokka
Helper IV
Helper IV

How to sum up numeric values in one column for each distinct text value in another column?

I have a table I created by dragging and dropping 2 different measures from our existing data warehouse into a new blank table.

Each of these measures is from different tables.

The table I have created on my desk top is from clicking the Table visual under Visuals.

These 2 tables are measures from our read-only data warehouse.

I cant change or alter column definitions ( e.g. for summing ) unfortunately.

Under these conditions, my Power Query is also disabled.

 

This is what the table on my powerbi desktop looks like :

 

[Alpha_Value]          [Sales]

AE1                          10

AE1                          20

DB3                          5

DB3                          25

DB3                          30

etc

 

What I want to do is for each distinct value in the [Alpha_Value] column ( AE1, DB3, etc ) , to add up ( sum ) all the corresponding numeric values in the [Sales] column.

 

So I would like to have a measure I can drag and drop onto my desktop that creates a new table on the same page that looks like this :

 

[Alpha_Value]        [Sales_Sum]

AE1                        30

DB3                        60

etc

 

Any help appreciated.  🙂

 

 

1 ACCEPTED SOLUTION
v-veshwara-msft
Community Support
Community Support

Hi @wokka ,
Thanks for posting in Microsoft Fabric Community.

To achieve the aggregation by each distinct Alpha_Value, you may consider creating a measure like this:

Sales_Sum = 
SUMX(
    VALUES(AlphaSalesTable[Alpha_Value]),
    CALCULATE(SUM(AlphaSalesTable[Sales]))
)

When you add the Alpha_Value column and this Sales_Sum measure to your Table visual, it should display the summed sales for each distinct Alpha_Value as you expected.

 

I reproduced the scenario using sample data.

Sample Data:

vveshwaramsft_0-1748593765830.png

Measure:

Sales_Sum =
SUMX(
    VALUES(AlphaSalesTable[Alpha_Value]),
    CALCULATE(SUM(AlphaSalesTable[Sales]))
)
 
Output(Table visual)
vveshwaramsft_1-1748593828414.png

 

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.


Thank you.

 

Attaching .pbix file for reference.

 

View solution in original post

8 REPLIES 8
v-veshwara-msft
Community Support
Community Support

HI @wokka ,

May I ask if the solutions provided has addressed your needs? If so, please consider marking the helpful responses as Accepted Solution to help others with similar queries.

If you need any further assistance, feel free to reach out.

Thank you.

v-veshwara-msft
Community Support
Community Support

Hi @wokka ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

v-veshwara-msft
Community Support
Community Support

Hi @wokka ,

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you.

v-veshwara-msft
Community Support
Community Support

Hi @wokka ,
Thanks for posting in Microsoft Fabric Community.

To achieve the aggregation by each distinct Alpha_Value, you may consider creating a measure like this:

Sales_Sum = 
SUMX(
    VALUES(AlphaSalesTable[Alpha_Value]),
    CALCULATE(SUM(AlphaSalesTable[Sales]))
)

When you add the Alpha_Value column and this Sales_Sum measure to your Table visual, it should display the summed sales for each distinct Alpha_Value as you expected.

 

I reproduced the scenario using sample data.

Sample Data:

vveshwaramsft_0-1748593765830.png

Measure:

Sales_Sum =
SUMX(
    VALUES(AlphaSalesTable[Alpha_Value]),
    CALCULATE(SUM(AlphaSalesTable[Sales]))
)
 
Output(Table visual)
vveshwaramsft_1-1748593828414.png

 

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.


Thank you.

 

Attaching .pbix file for reference.

 

Arul
Super User
Super User

@wokka ,

Make sure the [Sales_Sum] field is being aggregated as Sum in the visual.

To check this:

  1. Go to the Fields pane.

  2. Find the [Sales_Sum] field.

  3. Right-click on it and choose "Aggregate" → "Sum", or click on the dropdown next to the field in the Values area of the visual and select "Sum".

Alternatively, if you want more control or need dynamic aggregation, you can create a measure like this:

Sales_Sum = SUM(YourOriginalTable[Sales])

Then, use this measure in your Table visual instead of the raw [Sales] field.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Hi, thanks I tried both this things but it didnt seem to work.

Cookistador
Super User
Super User

Hello @wokka 

 

Normally, by default it should make the sum

Cookistador_0-1748416787629.png

 

I'm seeing two reason:

-Sale is text type and not numerical values

-The default aggregation is changed

For the aggregation, can you look in your table, if you can change it ?

Cookistador_1-1748416877858.png

 

 

By measure, do you mean that the measure has been created in your DWH or it is just a field comming from your DWH ?

Hi,

 

Its a measure coming from our data warehouse and so I tried your idea, but it wont let me change it to summarize unfortunately.

 

When i click on that column in my table under Column Tools its greyed out, I think because its coming from our data warehouse?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.