Tough question - summing based on text value in column

[wokka]

I have a table on my powerbi desk top is created by clicking the "Table" visual under Visuals.

I then drag and drop 2 different measures from our existing data warehouse into this new blank table.

Each of these measures is from different dimension tables 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 much appreciated.  

[v-menakakota · Accepted solution]

Hi @wokka ,
Thanks for reaching out to the Microsoft fabric community forum.

Try this simple measure.

Enter the following DAX code:

Sales_Sum = 
CALCULATE(SUM(SalesData[Sales]))

After creating the measure, you can drag and drop it into your table visual.

Find attached pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team 

[Poojara_D12 · Accepted solution]

Hi @wokka 

You have a table visual in Power BI Desktop showing two measures: [Alpha_Value] and [Sales], where [Alpha_Value] comes from one dimension table and [Sales] from another. Because your data is read-only and you cannot alter column definitions or use Power Query, you want to create a new measure that sums [Sales] for each distinct [Alpha_Value], effectively aggregating the sales by these alpha values.

To achieve this in Power BI, you can write a DAX measure that sums sales grouped by the distinct alpha values. Since your existing [Sales] is a measure and [Alpha_Value] is from a dimension, you can use the SUMX function combined with VALUES to iterate over distinct alpha values and sum corresponding sales.

For example, create a new measure called Sales_Sum with the following DAX:

Sales_Sum = 
SUMX(
    VALUES('YourDimensionTable'[Alpha_Value]),
    CALCULATE([Sales])
)

Then, in your Power BI report, create a new table visual and add [Alpha_Value] from your dimension table and this new measure Sales_Sum. This table will show each distinct Alpha_Value with the summed Sales.

If your Alpha_Value and Sales come from separate tables without a direct relationship, you may need to create a relationship between them or use DAX functions like RELATEDTABLE or TREATAS to bridge the tables for correct filtering.

In summary, even without modifying the underlying data or using Power Query, you can aggregate and display grouped sums in Power BI by leveraging DAX measures and existing relationships or table structures.

[v-menakakota · Accepted solution]

Hi @wokka ,
Thanks for reaching out to the Microsoft fabric community forum.

Try this simple measure.

Enter the following DAX code:

Sales_Sum = 
CALCULATE(SUM(SalesData[Sales]))

After creating the measure, you can drag and drop it into your table visual.

Find attached pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team 

[v-sshirivolu]

Hi @wokka ,
Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-sshirivolu]

Hi @wokka ,
Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-sshirivolu]

Hi @wokka ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solutionand give a 'Kudos'. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[bhanu_gautam]

@wokka Go to the "Modeling" tab in Power BI Desktop.
Click on "New Measure".
Enter the following DAX formula for the new measure:

DAX
Sales_Sum =
SUMX(
SUMMARIZE(
YourTableName,
YourTableName[Alpha_Value],
"TotalSales",
SUM(YourTableName[Sales])
),
[TotalSales]
)

After creating the measure, you can drag and drop it into your table visual.

[wokka]

Hi

I tried that, thanks.

What it does is create an ever increasing value on the [Sales] column.