Consolidating Data By City

[AaronGlenn10]

All Stars:

I have a table of sales data associated with restaurants located in various cities - for example there are 1,600 rows for Dallas, and 1,300 rows for Austin representing each reporting restaurant.  How can summarize or "roll up" by city?  So instead of 1000 rows for Dallas and 800 rows for Austin, I would have one row for each city with data consolidated?

Thank you!

~AGS

[v-jayw-msft]

Hi @AaronGlenn10 ,

If I understand you correctly, you could use SUMMARIZE() function.

https://docs.microsoft.com/en-us/dax/summarize-function-dax 

Best Regards,

Jay

[parry2k]

@AaronGlenn10 you need to provide sample data and expected output, it is not easy to make out what you are trying to do. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[AaronGlenn10]

Here's a sample of the data for Dallas.

Expected output would be a consolidated total of what's circled above for each city in Texas.

Dallas Total Beer Receipts, Total Wine Receipts, Total Liquor Receipts, etc.

Houston Total Beer Receipts, Total Wine Receipts, Total Liquor Receipts, etc.

Austin Total Beer Receipts, Total Wine Receipts, Total Liquor Receipts, etc.

Etc...

[AaronGlenn10]

@parry2k - did the screenshot above help?  Please let me know what additional information I can share, and really appreciate your assistance.  ~AGS

[PaulDBrown]

@AaronGlenn10 

Let DataTable be your fact table.

Create a measure for sum of sales:

Sum of sales  = SUM(DataTable [Sales])

Now create a table visual for example, add the field (column) from your DataTable for the cities and the measure [Sum of sales]

[AaronGlenn10]

Thank you for the reply!

I'm attempting to create a new table to use as a "summary table".  I have multiple measures for each restaurant- e.g., MTD sales, YTD sales, Trailing 12 Month sales, etc. within the Data Table for each restaurant within each city. Is it possible to reference those measures in the new table so that I can summarize or group by city?  For example:  I would like to see the total, combined MTD sales, the average MTD sales, the total YTD sales, the average YTD sales, etc. for the 1,000 restaurants in Dallas.

[parry2k]

@AaronGlenn10 you can simply add a following measure and then in the table visual, use column that contains city name and then measure

Total Sales = SUM ( Table[Sales] )

if this doesn't work, share some sample data for the solution.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡