Sum Based on Criteria Where Line Items are grouped by Project ID and then Filter by the Sum Total

[Tangi12]

Hi!

I am a semi newbie at Power BI. Normally I can do most things in PBI desktop through queries. However, our company has switched the availability of data to us through Analysis Services. We are not allowed to import the data and Analysis Services turns off many features.  I need to create a group for Small, Medium and Flow projects. 

I have a table that gives Order ID.  The Order ID can appear multiple times in the table. I want the summarized total >=700K to be considered "Large".  Order IDs summarized at >=300K and <700K to be considered "Medium" and anything below 300K considered Flow. 

Is there a way to create a Dax formula to summarize the Order IDs and filter them by the summarized value?  I cannot create new tables and I can't modify queries due to the connection to the model, so I would have to do this through a Dax formula.

I would appreciate any help you can give! 🙂

[Anonymous · Accepted solution]

[Order Tier] =
var __oneOrderVisible = HASONEVALUE( Orders[OrderId] )
var __orderTotal =
	CALCULATE(
		SUM( Order[Amount] ),
		VALUES( Orders[OrderId] ),
		ALL( Orders )
	)
var __tier =
	switch( true(),
		__orderTotal >= 700 * 1000, "Large",
		__orderTotal >= 300 * 1000, "Medium",
		"Flow"
	)
return
	IF( __oneOrderVisible, __tier )

This is a measure that for an OrderId will give you the tier the order is in. I don't fully understand the problem, so it might not be what you wanted. I just tried to imagine what you have. I assumed you've got a table Orders and in it a field called OrderId. I have no idea what other columns you might have in there. You could use this measure inline in a query and then filter by its results...

Best

D

[Tangi12]

Thank you! I will give this a try.