Forum Discussion
Tough question - summing based on text value in column
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.
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 itBest Regards,
Sreeteja.
Community Support TeamHi 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.
7 Replies
Anonymous 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.
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 itBest Regards,
Sreeteja.
Community Support TeamHi Anonymous ,
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.
Hi Anonymous ,
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.
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.
