Table with state vs Percentage value (need to display under different group if percentage <1% )

[tina345]

Hello, 

I am fairly new to Power BI and I have a table  below.

Depending on couple of slicer selection I displayed Aggregate value of amount , Total Count along with % for each fields for that selection.

My requirement is in case if any state/states has %Aggregate Value < 1% . then group it as State = 'Others'.

Multiple states may have <1% Aggregate Value and in that case, we have to group it under State = 'Others'.

Can this be achieved using some calculations?

I am bringing Aggregate Value and Total Count from database query.

Bringing % from database woundn't interact properly for different slicer selection.

Sample Data Set:

 There is a slicer with 'Check type', which can be selected all and get AVG:

 For  '% of Agg Value' and '% of Number' , I am using a measure below:

       m_Sum_Agg_Val_1 = sum(my_table[AVG]) and showing value as Percent of grand total

ORDER_NUM	LABLE_AMT	State	CHECK_TYPE	DATE_FILTER	AVG	No of Accounts
1	Gross Amount	AR	E Contract	Last Week	77454.00	1
1	Gross Amount	AZ	Funded On Other Day	Last Week	116483.00	1
1	Gross Amount	CA	E Contract	Last Week	73248.41	11
1	Gross Amount	CA	Funded On Other Day	Last Week	92495.31	14
1	Gross Amount	CT	E Contract	Last Week	70396.65	1
1	Gross Amount	DE	Funded On Other Day	Last Week	77642.96	1
1	Gross Amount	DE	E Contract	Last Week	81601.00	1
1	Gross Amount	FL	Funded On Other Day	Last Week	61691.30	10
1	Gross Amount	FL	E Contract	Last Week	92721.31	3
1	Gross Amount	GA	Funded On Other Day	Last Week	80286.82	1
1	Gross Amount	IL	Funded On Other Day	Last Week	88668.62	4
1	Gross Amount	IL	E Contract	Last Week	123593.71	2
1	Gross Amount	IN	E Contract	Last Week	64476.25	1
1	Gross Amount	KY	E Contract	Last Week	60590.02	1
1	Gross Amount	LA	Funded On Other Day	Last Week	104490.20	1
1	Gross Amount	MI	E Contract	Last Week	70549.03	5
1	Gross Amount	MI	Funded On Other Day	Last Week	104764.00	3
1	Gross Amount	MN	E Contract	Last Week	75416.43	1
1	Gross Amount	MO	Funded On Other Day	Last Week	77695.00	1
1	Gross Amount	NC	Funded On Other Day	Last Week	109193.75	1
1	Gross Amount	NJ	E Contract	Last Week	61449.00	1
1	Gross Amount	NJ	Funded On Other Day	Last Week	74550.09	2
1	Gross Amount	NM	Funded On Other Day	Last Week	77480.45	1
1	Gross Amount	NY	Funded On Other Day	Last Week	75838.19	7
1	Gross Amount	OH	Funded On Other Day	Last Week	75770.43	2
1	Gross Amount	PA	Funded On Other Day	Last Week	104790.00	1
1	Gross Amount	SC	Funded On Other Day	Last Week	74722.80	2
1	Gross Amount	TN	Funded On Other Day	Last Week	80154.94	1
1	Gross Amount	TX	E Contract	Last Week	77554.52	6
1	Gross Amount	TX	Funded On Other Day	Last Week	83710.83	10
1	Gross Amount	VA	Funded On Other Day	Last Week	108468.44	1
1	Gross Amount	WI	Funded On Other Day	Last Week	100.00	1

Thanks

[Ritaf1983]

Hi @tina345 
If i understood you correctly  you can add a calculated column with the formula :
Fixed state = if ('your table'[%Aggregate Value ] < 0.1 , "Others" , your table'[state]

if it is something else :

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

If this post helps, then please consider Accepting it as the solution to help the other

members find it more quickly

[tina345]

For some reason I don't see an option to attach data set or report.I instead pasted data just in case.

I created a measure which would give me %value for each category of states:

m_pct_test = Data_Set[m_pct_of_GT]/ calculate ( Data_Set[m_pct_of_GT] , ALLSELECTED())

After that I applied both of this logic below, for testing I set threshold value to 3%, however there are some rows, which has PCT > 3%, still appearing as 'Others'

New Value for STATE = switch ( TRUE(), [m_pct_test]  <= 0.03 , "Others" ,                                                                  Data_Set[State])

//if(Data_Set[m_pct_test] < .03 , "Others", Data_Set[State] )

This is my data set:

LABLE_AMT	State	CHECK_TYPE	DATE_FILTER	AVG	No of Accounts
Gross Amount	AR	E Contract	Last Week	77454	1
Gross Amount	AZ	Funded On Other Day	Last Week	116483	1
Gross Amount	CA	E Contract	Last Week	73248.41455	11
Gross Amount	CA	Funded On Other Day	Last Week	92495.305	14
Gross Amount	CT	E Contract	Last Week	70396.65	1
Gross Amount	DE	Funded On Other Day	Last Week	77642.96	1
Gross Amount	DE	E Contract	Last Week	81601	1
Gross Amount	FL	Funded On Other Day	Last Week	61691.299	10
Gross Amount	FL	E Contract	Last Week	92721.31333	3
Gross Amount	GA	Funded On Other Day	Last Week	80286.82	1
Gross Amount	IL	Funded On Other Day	Last Week	88668.615	4
Gross Amount	IL	E Contract	Last Week	123593.71	2
Gross Amount	IN	E Contract	Last Week	64476.25	1
Gross Amount	KY	E Contract	Last Week	60590.02	1
Gross Amount	LA	Funded On Other Day	Last Week	104490.2	1
Gross Amount	MI	E Contract	Last Week	70549.034	5
Gross Amount	MI	Funded On Other Day	Last Week	104764	3
Gross Amount	MN	E Contract	Last Week	75416.43	1
Gross Amount	MO	Funded On Other Day	Last Week	77695	1
Gross Amount	NC	Funded On Other Day	Last Week	109193.75	1
Gross Amount	NJ	E Contract	Last Week	61449	1
Gross Amount	NJ	Funded On Other Day	Last Week	74550.085	2
Gross Amount	NM	Funded On Other Day	Last Week	77480.45	1
Gross Amount	NY	Funded On Other Day	Last Week	75838.19286	7
Gross Amount	OH	Funded On Other Day	Last Week	75770.425	2
Gross Amount	PA	Funded On Other Day	Last Week	104790	1
Gross Amount	SC	Funded On Other Day	Last Week	74722.795	2
Gross Amount	TN	Funded On Other Day	Last Week	80154.94	1
Gross Amount	TX	E Contract	Last Week	77554.52167	6
Gross Amount	TX	Funded On Other Day	Last Week	83710.831	10
Gross Amount	VA	Funded On Other Day	Last Week	108468.44	1
Gross Amount	WI	Funded On Other Day	Last Week	100	1

Thanks