Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
Hi, @Anonymous
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.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
Hi, @Anonymous
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
51 | |
27 | |
19 | |
18 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
23 | |
22 |