Forum Discussion

CaptainCrewe's avatar
CaptainCrewe
Frequent Visitor
9 years ago
Solved

Generate a table using SUMMARIZE and GROUPBY

  Hello   As a relative newbie to DAX, I often find myself going round and round getting tantalisingly close to a solution but missing the knowledge to make things work.  To date, head banging has...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi CaptainCrewe,

     

    You can try to use below formula to get the filter result table.

     

    Filtted Table = CALCULATETABLE(Table1,FILTER(ALL(Table1),CONTAINS(Tablel2,Tablel2[IssuerTicker],Table1[IssuerTicker],Tablel2[Max_Sum_NV_USD],Table1[Sum_NV_USD])))

     

     

    Notice: 'table1'(summarizecolumns), 'table2'(group by).

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

HI CaptainCrewe,

 

I modified your formula without test on real table, perhaps you can try it if it works on your side.

 

TestTable = 
VAR Table1 = SUMMARIZECOLUMNS(
				IssuerLookup[IssuerTicker],
				IssuerLookup[Analyst2],
				AssetLookup[Region Of Risk],
				AssetLookup[Country Of Risk],
				"Sum_NV_USD", CALCULATE(SUM(Fact_Assets[NominalAmountUSD]))
			)
VAR Table2 = SUMMARIZE(
				Table1,
				[IssuerTicker],
				"MaxSum", MAXX(FILTER(Table1,[IssuerTicker]=EARLISER([IssuerTicker])), [Sum_NV_USD])
			)
RETURN
	FILTER(
		Table1, 
		CONTAINS(Table2, [IssuerTicker], [IssuerTicker], [MaxSum], [Sum_NV_USD])
	)

 

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi, 

 

I am having a similar problem but can't figure out how to modify my headusre to make SUMMARIZE work.

 

I have a measure that isn't adding up the total correctly so i wanted to use SUMMARIZE to get around that issue but i am getting the error: "The column 'IRI/Spins Cust' specified in the 'SUMMARIZE' function was not found in the input table."

 

Measure that doesn't add up correctly at total: 

Actual Promo Spend = SUMX('Trade Plan 2019',[Actual Promo Units]*[Total Allowance Per Store $/Ea])
 
Summarize measure that is giving me error: 
Actual Promo Spend Total = SUMMARIZE('Trade Plan 2019','Customer Lookup'[IRI/Spins Cust],"Actual Promo Spend",[Actual Promo Spend])
 
Table with data: