Forum Discussion

tina345's avatar
tina345
Helper II
2 years ago

Aggregate based on existing data set

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.
 

 


 


 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      tina345
      Helper II
      2 years ago

      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