topic Re: Aggregation issue with a string measure on % of grand total calculations in DAX Commands and Tips

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

Tina91 — Sun, 24 Mar 2024 16:17:15 GMT

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

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

lbendlin — Sun, 24 Mar 2024 17:42:15 GMT

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.

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

Tina91 — Sun, 24 Mar 2024 19:05:41 GMT

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

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

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

tamerj1 — Mon, 25 Mar 2024 05:36:51 GMT

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.