Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to create an aggragated table based on a table within my Power BI app.
Basically I have the following fields in the base tablein my Power BI app:
Company
Revenue
Sale Date
I want to get a Calculated table base on the table above that does the following:
Company : Group by
Revenue: Sums
Sale Date: Get the most recent sale date
Thanks,
Z
Hi,
Using the Query Editor, right click on the Company Name heading > Group By. Select SUM as the function for Revenue and MAX as the function for Sale Date.
Thanks but I need a new table with these aggragations not an aggregation of the current table.
@Anonymous
= SUMMARIZECOLUMNS( Sales[Company], "SaleAmount", SUM( Sales[Revenue] ), "LastDate", MAX( Sales[Sale Date] ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Sorry - left out one question. How do I add a filter to your expression - say I only want company Sales from the "East" region?
@Anonymous
You have different Table function within DAX, CALCULATETABLE and SUMMARIZECOLUMNS are just two of them.
If your region column is within your sales table then you can do:
= CALCULATETABLE( SUMMARIZECOLUMNS( Sales[Company], "SaleAmount", SUM( Sales[Revenue] ), "LastDate", MAX( Sales[Sale Date] ) ), Sales[Region] = "East" )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Awesome Thanks! So "SUMMARIZECOLUMNS" has the same effect as "CALCULATETABLE" - either of them can be used to create a new table from a current table in your Power BI app?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |