DAX calculating Minimum of several Summed values in a matrix

[sqlsasha]

Hi team. I need some help with calculating Minimum of several Summed values.

I have a table with columns:

Category

Month

Sales Value

I need to have a visual (table\matrix\etc.) with

- 2 axis columns: Category, Month

- and with 2 measures: just simply Sum(Sales Value), and another measure which will show minimum by category for a list of sums per month.

So for example, I need to have:

	Month 1	Month 1	Month 2	Month 2	Month 3	Month 3
	Sum Sales	Min Measure	Sum Sales	Min Measure	Sum Sales	Min Measure
Category 1	10	10	20	10	30	10
Category 2	200	100	100	100	300	100
Category 3	5000	3000	4000	3000	3000	3000

10 is minimum of monthly sum(sales) per Category 1, so I need it in my Min Measure. The same for all other Categories.

Adding some saple report with data: https://www.sendspace.com/file/uhf89l 

Calculating an intermediate calculated table is not an option.

I really feel, that MINX might be my answer, but I can't finilize the measure by myself.

Thanks in advance

[v-kelly-msft · Accepted solution]

Hi @sqlsasha ，

Create 2 measures as below:

_min = CALCULATE( MINX(Months,[Sales Sum]),ALL(Months))

_total = 
CALCULATE(SUM('Orders'[Sales]))

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[sqlsasha · Accepted solution]

Thank you, team!

I also came (with some additional help) to the solution.

Will take some time to tottaly get into it.

Thanks again for such fast help.

[v-kelly-msft · Accepted solution]

Hi @sqlsasha ，

Create 2 measures as below:

_min = CALCULATE( MINX(Months,[Sales Sum]),ALL(Months))

_total = 
CALCULATE(SUM('Orders'[Sales]))

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@sqlsasha , Try one of the two

minx(summarize(Table, table[category], "_1",calculate(min(Table[Sales]),allexcept(Table,Table[category]))),[_1])

Or
minx(summarize(Table, table[category], "_1",calculate(min(Table[Sales]),all(Table[Month]))),[_1])