Combine values of multiple rows in one row

[oandroido]

Hi,

Still very new to Power BI and trying to figure things out...

I have two columns; the first has States, and the second has # of sales per day. So, each state is listed multiple times, and I have a lot of data for sales per day as well.

I'd like to be able to combine this in a few ways, the first being keeping States to a single state per row, and averaging out the # of sales per day on that line.

That said, I'm not sure what the process(es) might be called, and haven't found the solution by searching.

The second part of this is a bit more complicated: I have dates for everything as well, and would also like to be able to view by Month, for example. If that's too much for one post I'll create a new thread.

Thanks in advance!

[BobBI]

Hi Oandroido,

Can you please share some sample data so provide your step by step solution.

thanks,

SS

[oandroido]

Sure thing - 

State	Sales Per Day	Date
Pennsylvania	41	1/24/2018
Pennsylvania	44	2/14/2018
Pennsylvania	72	4/25/2018
Pennsylvania	82	7/19/2018
Pennsylvania	27	8/8/2018
Florida	22	2/1/2018
Florida	48	1/24/2018
Florida	37	5/4/2018
Florida	25	3/12/2018
Florida	72	7/14/2018
Florida	73	4/4/2018
Florida	22	4/5/2018
California	26	3/26/2018
California	26	9/5/2018
California	73	10/3/2018
California	16	8/14/2018

[BobBI]

Thanks

1)  Create a mesure in sales table to calculate average.

Avg Sale/day = CALCULATE(AVERAGE(Sales[Sales Per Day]),ALLEXCEPT(Sales,Sales[State]) )

2) Best practice -  Create a Date Table

Date = CALENDAR(date (2018,01,01),DATE(2018,31,12))

Add a column for 'Month'

Month = FORMAT('Date'[Date],"MMM")

link Date table with original sales table , Drag month from Date table and sales per day from sales table. ( second appreach would you Create month column within sales if you dont want to create date table but it is best practice to have date table)

Hope this helps,

SS

[oandroido]

Hi Bob-

Sorry, still pretty new to this - could you explain what you mean when you say "Best practice -  Create a Date Table" ?

thanks

[Anonymous]

In the query editor select the State column then use the group by function, it's fairly self explanitory.

[oandroido]

Thanks Tom - when I do that, it creates the correct table in the query editor, but when I Close & Apply, it breaks my visualizations and sends an error.

[Anonymous]

Did you rename any columns?

When you group by it sometimes puts a prefix iirc

[oandroido]

Hi Tom - I didn't rename anything.