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. 🙂

Need Help

v-veshwara-msft
1 year ago
Hi Anonymous ,
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:

Measure:

Sales_Sum =

SUMX(

VALUES(AlphaSalesTable[Alpha_Value]),

CALCULATE(SUM(AlphaSalesTable[Sales]))

)

Output(Table visual)

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.
How to sum up numeric values in one column for each distinct text value in another column.pbix21 KB

8 Replies

Cookistador
Super User
1 year ago
Hello Anonymous

Normally, by default it should make the sum

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 ?

By measure, do you mean that the measure has been created in your DWH or it is just a field comming from your DWH ?
- Anonymous
  Not applicable
  1 year ago
  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?
Arul
Super User
1 year ago
Anonymous ,

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

To check this:

Go to the Fields pane.

Find the [Sales_Sum] field.

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.
- Anonymous
  Not applicable
  1 year ago
  Hi, thanks I tried both this things but it didnt seem to work.
v-veshwara-msft
Community Support
1 year ago
Hi Anonymous ,
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:

Measure:

Sales_Sum =

SUMX(

VALUES(AlphaSalesTable[Alpha_Value]),

CALCULATE(SUM(AlphaSalesTable[Sales]))

)

Output(Table visual)

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.

How to sum up numeric values in one column for each distinct text value in another column.pbix21 KB
v-veshwara-msft
Community Support
1 year ago
Hi Anonymous ,

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
1 year ago
Hi Anonymous ,

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
1 year ago
HI Anonymous ,

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.