Generate a table using SUMMARIZE and GROUPBY

[CaptainCrewe]

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 eventually got me a solution, but not today.

I have a table of Issuers, the relevant fields being IssuerID, Ticker and Analyst.  The same Ticker can be used across multiple IssuerIDs.

I have a table of Assets, with relevant fields of AssetID, IssuerID, Region, Country and NominalAmountUSD.  The join is from IssuerLookup (1) to AssetLookup (Many), on the IssuerID.

My task is to show reports by Ticker, Analyst, Region and Country.  An Issuer can have several Assets and hence multiple region/country combinations, but I'm being asked to extract just one to report on.  The business rule is to use the combination that has the highest NominalAmountUSD - that value comes from a Fact table joined to the Asset table.

I've learnt how to create a calculated table, and that's a good first step.  The following query lets me group across IssuerIDs that share a Ticker:

SUMMARIZECOLUMNS(
    IssuerLookup[IssuerTicker],
    IssuerLookup[Analyst2],
    AssetLookup[Region Of Risk],
    AssetLookup[Country Of Risk],
    "Sum_NV_USD", CALCULATE(SUM(Fact_Assets[NominalAmountUSD]))
)

That gives me good groupings, so the next step is to extract the Analyst, Region and Country for a given ticker where Sum_NV_USD is the maximum.  Having made a virtual table from the query above, called IssuerTickerToCountryOfRisk, the following query finds me the maximum SuNV_USD for a given Ticker:

GROUPBY(
    IssuerTickerToCountryOfRisk,
    IssuerTickerToCountryOfRisk[IssuerTicker],
    "MaxSum", MAXX(CURRENTGROUP(), IssuerTickerToCountryOfRisk[Sum_NV_USD])
)

But I cannot figure out how to combine these to generate a final calculated table that just has the one row per ticker, with the Region and Country coming from the combination with the highest NominalAmountUSD.  I'd like to use CALCULATEDTABLE but the filter argument for that can't use aggregates.  I've experimented with various combinations of ADDCOLUMNS and SUMMARIZE, but without success.

I'd like to have a calculated table in the model with these results so as to refer to it from more than one Power BI Visual.  Any guidance on achieving this will be much appreciated.

[Anonymous]

Hi @CaptainCrewe,

Can you provide some sample data to test?

Regards,

XIaoxin Sheng

[CaptainCrewe]

Thank you for your response.

I'm not sure in what form you need the sample data.  For now, I'll show a couple of extracts from my DAX Studio queries.  The first shows the initial grouping by Ticker, Analyst, Region and Country with a Sum of the NominalAmount to be used as a tie-breaker, obtained from the SUMMARIZECOLUMNS function:

IssuerTicker	Analyst2	Region Of Risk	Country Of Risk	Sum_NV_USD
AAA	Jim Smith	Africa	Morocco	600000
BBB	Amy Bloggs	North America	Canada	1656090590
CCC	Mary Doe	South America	Chile	894679350
DDD	Joe Brown	Asia	China	22909716756
DDD	Joe Brown	Asia	Hong Kong	18898282952
DDD	Joe Brown	Asia	Macao	1762612500
DDD	Joe Brown	Asia	Singapore	562370067
DDD	Joe Brown	Australia	Australia	8865500
DDD	Joe Brown	Europe	France	1185432644
DDD	Joe Brown	Europe	Luxembourg	11250000
DDD	Joe Brown	Europe	United Kingdom	552160000
EEE	Mary Doe	South America	Chile	1293134082
FFF	David Jones	North America	Canada	3179842513

I turned this into a calculated table in my model (IssuerTickerToCountryOfRisk) so as to query it more easily and find the MAX(SUM_NV_USD).  

Using the GROUPBY function, I can find the maximum Sum_NV_USD for a given ticker:

IssuerTicker	Max_Sum_NV_USD
AAA	600000
BBB	1656090590
CCC	894679350
DDD	22909716756
EEE	1293134082
FFF	3179842513

What I'm aiming for is a calculated table with only one row per ticker.  Where a ticker has more than one combination of Region and Country (ie DDD in this extract), then I need to select the row with the highest Sum_NV_USD.  Easy to do in SQL and I fear I may be bringing too much of that mindset to this problem.  I feel there must be a more elegant way to return the desired rows.

IssuerTicker	Analyst2	Region Of Risk	Country Of Risk	Sum_NV_USD
AAA	Jim Smith	Africa	Morocco	600000
BBB	Amy Bloggs	North America	Canada	1656090590
CCC	Mary Doe	South America	Chile	894679350
DDD	Joe Brown	Asia	China	22909716756
EEE	Mary Doe	South America	Chile	1293134082
FFF	David Jones	North America	Canada	3179842513

The final objective is to use this summarized table of ticker, analyst, region and country as a replacement lookup table for reports where a single analyst, region and/or country is wanted for a given ticker.  The Sum_NV_USD isn't needed since it can come from a measure calculation over the underlying fact table.

Thanks again for taking an interest

[Anonymous · Accepted solution]

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

[CaptainCrewe]

That's wonderful.  Works a treat.  In my wanderings to date, I hadn't noticed or thought to try CONTAINS.

This will certainly get me going and I thank you for your good understanding of my problem.  As a matter of interest and to increase my DAX knowledge, are you able to confirm how my source queries and your solution might be combined?  It would be much neater to have one statement and one table rather than three.

I think the approach would be to use table variables, but I'm confused by the scope of these.  I tried the following and get the error "The column 'IssuerTicker' specified in the GROUPBY function was not found in the input table":

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 = GROUPBY(
				Table1,
				Table1[IssuerTicker],
				"MaxSum", MAXX(CURRENTGROUP(), Table1[Sum_NV_USD])
			)
RETURN
CALCULATETABLE(
	Table1,
	FILTER(
		ALL(Table1), 
		CONTAINS(Table2, Table2[IssuerTicker], Table1[IssuerTicker], Table2[MaxSum], Table1[Sum_NV_USD])
	)
)

Looking around, including your post of 08-29-2016, I suspect Table2 above can't accept Table1 as part of its definition.  Do you think there is a way to combine all these into one calculated table definition?

This is all a 'nice to have'.  I'm very grateful for your good assistance in solving the main problem.

[Anonymous]

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]

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:

[CaptainCrewe]

It does indeed work.  I'll have to study the differences between my attempt and your solution.  On the face of it, looks like GROUPBY isn't useful in this context - though your translation of its objective via a SUMMARIZE is very useful.

I also note how you reference the columns in the table variable.  All extremely helpful.

Many thanks for helping me over this hurdle and for impressing me afresh with the power of DAX.