Aggregate based on existing data set

[tina345]

Hi All,

I have a requirement when '% of Average' < 3%, group them as "State = Others".

Initially I tried to do this simply by creating a calculated column, however my dataset is using multiple slicers and it didn't gave me right '%Value'. I am getting Average values from database, for % calc, I am using a measure 'm__Sum_Agg_Val' & displaying as '% of Grand Total'.

I had to create a measure on column - State , which will be used on 'm_State_Updated_'.

m_State needs aggregation, for which I used min value (if there is any better way , please let me know).I was able to derieve "Others" as state however now issue is on grouping.On below data set if I remove 'State' and just keep 'm_State_Updated_', it will end up with only Min State name (i.e AR).

I tried to summarize this table and group by 'm State Updated', but didn't allowed summarization based on a measure 'm_State_Updated_', so it didn't help me.

m__Sum_Agg_Val = sum(TEST_DATA_SET[AVG])
m_pct_test = [m__Sum_Agg_Val]/( CALCULATE([m__Sum_Agg_Val] , ALLSELECTED()))
m State = min ( TEST_DATA_SET[State] )
m_State_Updated_ = if( TEST_DATA_SET[m_pct_value] < 0.03, "Others", TEST_DATA_SET[m State] )

From below data set I need to summarize based on 'm State Updated' and sum all values for state = Others.I have been trying this but couldn't find right solution.

if I remove State and just keep 'm_State_Updated' , it will just give min value.

[Anonymous]

Hi, @tina345 

This is a conceptual approach. You may need to adjust the logic based on the exact dataset structure and requirements.

m_State_Grouped = 
VAR AvgThreshold = 0.03
VAR SummaryTable = SUMMARIZE(
    TEST_DATA_SET,
    TEST_DATA_SET[State],
    "TotalAvg", [m__Sum_Agg_Val],
    "PctOfTotal", [m_pct_test]
)
VAR FilteredTable = FILTER(
    SummaryTable,
    [PctOfTotal] < AvgThreshold
)
RETURN
IF(
    ISBLANK(SUM(FilteredTable[TotalAvg])),
    "Others",
    MIN(TEST_DATA_SET[State])
)

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tina345]

Thanks for the reply.

I tried to create a measure however I am getting a msg saying " Table Variable 'FilteredTable' cannot be used in current context because a base table is expected.

I don't see any option to upload file either so I pasted some sample data below in csv format.

Expected result : if % of aggregate value < 3% , state = 'Others', multiple states can be categorized as 'Others' and that is ok.Finally summarization should summarize it together.

Below is a sample CSV format data set (sorry I don't see any way to attach a file or data) :

Amount Type,State,Aggregate Value,Percentage of Aggregate Value,No of Accounts,Percentage of No of Accounts
Gross Amount,AL,71590,2.99%,1,1.20%
Gross Amount,AZ,61419,2.56%,1,1.20%
Gross Amount,CA,147961.8735714286,6.17%,21,25.30%
Gross Amount,CO,71089,2.97%,1,1.20%
Gross Amount,DE,80729.78,3.37%,1,1.20%
Gross Amount,FL,138375.3142857143,5.77%,16,19.28%
Gross Amount,ID,99369,4.15%,1,1.20%
Gross Amount,IL,163865.54499999998,6.84%,3,3.61%
Gross Amount,IN,80385,3.35%,1,1.20%
Gross Amount,KY,72625.23,3.03%,1,1.20%
Gross Amount,MI,152647,6.37%,7,8.43%
Gross Amount,MN,68750,2.87%,1,1.20%
Gross Amount,MS,65190,2.72%,1,1.20%
Gross Amount,MT,104783.68,4.37%,1,1.20%
Gross Amount,NC,65910,2.75%,1,1.20%
Gross Amount,NJ,122308,5.10%,2,2.41%
Gross Amount,NY,131689.72666666665,5.49%,4,4.82%
Gross Amount,OH,60209.16,2.51%,1,1.20%
Gross Amount,PA,66805.76,2.79%,1,1.20%
Gross Amount,SC,67565,2.82%,1,1.20%
Gross Amount,TN,73482.48,3.07%,1,1.20%
Gross Amount,TX,140940.90714285715,5.88%,9,10.84%
Gross Amount,VA,205670.965,8.58%,3,3.61%
Gross Amount,WA,83441.82666666668,3.48%,3,3.61%

Thanks