Sum by categories but group all sums of less than x into new category

[AlexB88]

Hi everyone,

Would really appreciate help with this.

(using powerpivot and pivottable not PowerBI)

I have multiple category columns, and 2 key data columns, cost and count. I would like to sum the data and the count by category (easy enough), but if the count of either country or sub category is less than 10 (or x), it would create a new category 'other'. 

This would reduce the 'tail of a large data set by grouping all small values together (but based on total count, not the total sum), but the numbers would still add up to the correct number. 

The data will get summarised by country, and sub category therefore ideally the measure would work if used in either scenario.

Example of base data and desired output below (had tried to attach a file but unable)

Base data:
Country	Sub Category	Cost	Count
Country 1	Sub 1	10,000	5
Country 1	Sub 2	200,000	124
Country 2	Sub 1	4,000	80
Country 2	Sub 2	75,000	1
Country 3	Sub 1	20,000	3
Country 4	Sub 2	600,000	15
Country 5	Sub 2	7,500	1
		916,500	229
Output report:
	Cost	Count
Country 1	210,000	129
Country 2	79,000	81
Country 4	600,000	15
Other	27,500	4
Total	916,500	229

Many thanks for any advice in advance.

(cant seem to attach a file)

[Jihwan_Kim]

Hi, @AlexB88 

Please check the below link, that is to the sample pbix file that I have created by using your sample data.

the result is shown as per your request.

One of the four measures that I have written below, which is for Cost total, including Other.

You can check other measures in the pbix file.

Cost Total =

VAR newtable =

ADDCOLUMNS (

SUMMARIZE ( ALL ( Data ), Data[Country] ),

"@cost", [Cost],

"@count", [Count]

)

VAR countrycountmorethanten =

FILTER ( newtable, [@count] > 10 )

VAR result =

IF (

SELECTEDVALUE ( Data[Country] ) = "Other",

SUMX ( newtable, [@cost] ) - SUMX ( countrycountlessmorethanten, [@cost] ),

IF ( [Count] <= 10, BLANK (), [Cost] )

)

RETURN

result

https://www.dropbox.com/s/sbykhkltc3s6xkq/AlexB88.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[AlexB88]

Hi Jihwan, many thanks for your response!

That seems to work well in PowerBI, is there any way to use the measure in PowerPivot? 

Thanks,

Alex

[Jihwan_Kim]

Hi, @AlexB88 

Thank you for your feedback.

I think Power Pivot also uses DAX. But I only tried a very simple one before.

I think it should work in the same way.

And, if it is in Power Pivot, that means it is in an EXCEL environment, and I think it might be easier to create the above.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.