Percentage of grand total problem

[Data_BS]

Hi community, 

I am trying to calculate the % of a grand total for a dimension column but with a slight twist. If a category's % of grand total is less than or equal to 10%, I'd like to bundle these categories into a group called "other" and the grand total of the "other" group should be the sum of their grand total %s.

I'd like the solution to be dynamic. For instance, I could select swap out different dimensions in a visual and this solution would work. Below is an example of what I'm trying to achieve. If anything is unclear, please shout. I'm open to any solution.

Example Data

Expected Output
Note, I've supplied two different tables in the solution. In PBI, I'd like just one table with the ability to pull the different dimensions into it.

Data used in problem

Country	Favourite Colour	Flag
UK	Red	1
USA	Red	1
France	Red	1
UK	Blue	1
UK	Black	1
UK	White	1
Germany	Green	1
Germany	Green	1
Germany	Blue	1
Germany	Pink	1

Thanks,

Ben

[v-yajiewan-msft]

Hi @Data_BS , hello ryan_mayu and SamWiseOwl, thank you for your prompt reply!

Is there any progress on this issue?

If you find any answer is helpful to you, please remember to accept it.

It will help others who meet the similar question in this forum.

Thank you for your understanding.

[ryan_mayu]

@Data_BS 

have you tried grouping and binning function?

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning?wt.mc_id=DP-M...

[Data_BS]

Hi @ryan_mayu, this is really interesting. I didn't know about this. I'll check this out when I have time (currently at work so I can't). 

If this solves the problem, I'll let you know. 

Thanks for the navigation! 

[SamWiseOwl · Accepted solution]

Hi @Data_BS 

You need to Append a new row to the table with "Other" in both the country and the colour columns.

This is used to check which row we are on in the visual.

Field Parameters: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

Let you swap which column is in the visual.

Create a measure:

Sales =

 var curr = CALCULATE(sum('Table'[Flag]))

 var total = CALCULATE(sum('Table'[Flag]),REMOVEFILTERS('Table'[Country],'Table'[Favourite Colour]))

 var per = curr/total

var sel = MAXX('Choose category',[Choose category])

 var colour = SUMMARIZE(ALLSELECTED('Table'),'Table'[Favourite Colour], "Percent",

  CALCULATE(sum('Table'[Flag]))/CALCULATE(sum('Table'[Flag]),REMOVEFILTERS('Table'[Country],'Table'[Favourite Colour])))

  var country = SUMMARIZE(ALLSELECTED('Table'),'Table'[Country], "Percent"

 ,CALCULATE(sum('Table'[Flag]))/CALCULATE(sum('Table'[Flag]),REMOVEFILTERS('Table'[Country],'Table'[Favourite Colour])))

var final =

SWITCH(

    TRUE()

    ,sel = "Country" && SELECTEDVALUE('Table'[Country]) = "Other"

    ,sumx(filter(country, [Percent] <=0.1), [Percent])

    ,SELECTEDVALUE('Table'[Favourite Colour]) = "Other"

    ,sumx(filter(colour, [Percent] <=0.1), [Percent])

    ,per <= 0.1

    ,BLANK()

    ,per)

return final

This tests if the selected item is Colour or Category, calculates which rows are below 10% and returns the appropriate calculation.

Hope this helps!

File here: Dynamic calculation.pbix

[Data_BS]

Hi @SamWiseOwl. I'm at work at the moment but I'll explore this as soon as I get time. Thanks for providing this, I'll keep you updated. Absolute legend.