Tough question - summing based on text value in column

Hi Anonymous ,
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 

Hi Anonymous 

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.