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

[wokka]

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

[v-veshwara-msft]

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]

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]

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 · Accepted solution]

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:

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.

[Arul]

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

[wokka]

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

[Cookistador]

Hello @wokka 

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 ?

[wokka]

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?