sum and group with max results per day

[Thoms]

Hi,

Friends, I need help with DAX  formula.

What i have :

city	product #	date	pcs
Beijing	123544	January 01 2019	1
Beijing	123544	January 01 2019	10
Beijing	123544	January 02 2019	15
Beijing	123544	January 02 2019	2
Shanghai	123544	January 01 2019	3
Shanghai	123544	January 01 2019	12
Shanghai	123544	January 02 2019	12

What i need is to make a measure which show only maximum results per day group by city, date and product pcs.

like this:

city	product	month	sum and maximum result per day (pcs)
Beijing	123544	201901	17
Shanghai	123544	201901	15

Please help

[v-lili6-msft]

HI, @Thoms 

Just try this simple way as below:

Step1:

Create a month column for your date column

Month = YEAR('Table'[date])*100+MONTH('Table'[date])

Step2:

Use this formula to create a measure to get your requirement:

Measure = var _table=SUMMARIZE('Table','Table'[city],'Table'[product #],'Table'[Month],'Table'[date],"_pcs",SUM('Table'[pcs])) return
MAXX(_table,[_pcs])

Result:

And here is pbix file, please try it.

Best Regards,

Lin

[Thoms]

If i add the days, table do not shows the total of the month

[v-lili6-msft · Accepted solution]

hi, @Thoms 

If you want to keep [date] field in the visual, just adjust the formula as below:

Measure 2 = var _table=CALCULATETABLE(SUMMARIZE('Table','Table'[city],'Table'[product #],'Table'[Month],'Table'[date],"_pcs",SUM('Table'[pcs])),ALL('Table'[date])) return
MAXX(_table,[_pcs])

Result:

Best Regards,

Lin

[Thoms]

Dear,

Thanks a lot! works perfect!

[v-lili6-msft]

hi, @Thoms 

You are welcome.  

please accept the reply as solution, that way, other community members will easily find the solution when they get same issue.

best Regards,

lin

[Thoms]

Hi,

Thankx for feedback! but how about if i need the column in the table, not measure?

[danno]

If you want to create a new table in power bi to summarise your data you can use the Modeling ribbon > New Table option and enter the following DAX
Sales Summary = SUMMARIZECOLUMNS(‘Product Sales’, [city], [product], “month”, FORMAT([date], “YYYYMM”), “Total pcs”, SUM[pcs], “Max pcs”, MAX[pcs]).

[Thoms]

Danno thanks,

Sorry, i might have put it purly. 

Acctually ill be fine with a measure which will make a sum of sales of particular product per day and find max of those sales among days, to find when this product was selling better.

[danno]

If you need help with measures let me know, otherwise please mark as solution

[Thoms]

Yes, i need a measure. 

[danno]

You need a column for the Month (if you dont already have a separate calendar table), that will alow you to group by month in the format you require.   The simple measures for max and total can be created as : Total pcs =  SUM([pcs]) and Max pcs = MAX([pcs]) and providing that you split the result by month you will see the results in your table / matrix on the report. 

[Thoms]

Ok, ill try to explain in other way.

How do i add a new column in table (sumup per day and max in month by prod and city)?

city	product #	date	pcs	sumup per day	max in month by product and city
Beijing	123544	January 01 2019	1	11	17
Beijing	123544	January 01 2019	10	11	17
Beijing	123544	January 02 2019	15	17	17
Beijing	123544	January 02 2019	2	17	17
Shanghai	123544	January 01 2019	3	15	15
Shanghai	123544	January 01 2019	12	15	15
Shanghai	123544	January 02 2019	12	12	15