Aggregation issue with a string measure on % of grand total calculations

[Tina91]

Hi All,

   I am having one issue aggregating on report. I have a 'Amount' and percentage of a grand total columns on 'Amount'     

   field. My requirement is if average value in % is < 2% , display State as 'Others' and after that aggregation should happen based on   

   new states.

   Below are my calculations/measures:

Column Name: Amount
% of Total_Amount = sum(Sheet9[AVG]) // Note:  displayed as '% of grand total'
m_State = max(Sheet9[State])                  
m_pct_value = Sheet9[% of Total_Amount]/ calculate(Sheet9[% of Total_Amount],ALLSELECTED())
m_Updated_State = switch ( TRUE(), [m_pct_value] <= 0.02, "Others" , Sheet9[m_State] )  // Note: measure don't allow reference to table.column, so had to create a measure 'm_state' above

Sample Data file and this PBI Report s under this location (I don't see attach option)

https://drive.google.com/drive/folders/14VkqgAHUkuow43oAlhyS0R_FlZ3bSl1g?usp=sharing

if I dis-select 'State' and keep updated value of State (measure: m_updated_state), I am expecting proper aggregation on all states, including new value 'Others'. If you observe closely depending on aggregation on m_State  (min/max) , it brings that State on subtotals in this case 'CA'.

Could you please help?

Thanks

[tamerj1 · Accepted solution]

Hi @Tina91 
Please refer to attached sample file with a proposed solution.

Here I'm aggregating the perentages only. Properly aggregating other metrics in the same table visual could be tricky. Please let me know what is you next step.

[lbendlin]

and after that aggregation should happen based on new states.

What aggregation? What is your expected outcome?

Note that you cannot measure a measure.  Materialize it, or create a new measure that includes the entire business logic.

[Tina91]

Final output should be based on each state including 'Others'.

So here instead of 'State', we need to aggregate based on 'm_state_updated'.