Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
Solved! Go to 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
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.
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.
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
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.
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.
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.
@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.
Proud to be a Super User! |
|
Hi
I tried that, thanks.
What it does is create an ever increasing value on the [Sales] column.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |