Measure based on individual related values vs Calculated Column vs Power Query

[devitus]

Hi,

I have recently begun rebuilding my Power BI dataset which our organizations client-facing reporting relies on. One of my main targets for this rebuild is to avoid "merge tables" queries and maximize use of relationships and measures as much as possible. The following stumbling block is what I'd like to kindly ask for advise with:

As you can see in an attached illustration, I have a CRM table that - among other data - contains account names and their respective fee %, as well as multiple tables containing advertising spend data from various sources. The spend figures in those tables are in their "net" format, i.e. excluding our fee. However, for reporting we need to convert those to "gross", i.e. add our fee on top.

The tables are linked with a one-to-many relationship using "Account Name" column.

What is the most efficient way to produce a "Gross Spend" value (Net Spend / (1-Fee %)) that would respect the Fee % of each individual account? By "efficient" I mean a solution that won't enlarge the dataset or prolong refresh times significantly (trying to avoid Calculated Columns or merging tables if possible) and won't severely affect report render time for an end user.

On the one hand, a measure with a "RELATED" function to look up respective account's Fee % for the calculation seems like a possible solution. However would that measure work if I were to look at a sum of all spend for e.g. a single month? In other words, would it be able to calculate fee for the said month based on Fee % value of each individual account, or would it fail as it'd be given more than one Account Name, and thus multiple Fee % values to deal with? I don't believe measures are capable of that as they normally work with sum / min. / max. / avg., etc. of a value, but not multitude of related values that need to be calculated individually for each Account Name within the given month.

My previous solution merged Spend Data 1, Spend Data 2 and other such tables into a single one, then pulled in Fee % based on the related Account Name value in CRM table and calculated Gross Spend line by line, all in Power Query. It works, but it is a non-elegant solution that had produced a "monster table" that slowed down refresh times and grew the dataset out of proportion.

Really looking forward to your advise on this.

Thanks in advance!

[Anonymous · Accepted solution]

// The Spend Data 1, 2, 3... should be
// one table as they have the same
// layout. Just use PQ to union them.
// You can add another column to this
// union that will tell you which table
// the data comes from.

// Based on this assumption:

[Gross Amount] =
// This measure could be slow as it
// needs to iterate the fact table. If you
// want to have a blasingly fast one, you need
// to precalculate the gross spend in
// Power Query. It'll then be the fastest
// since it will only have to sum up
// a column with the gross spend. YOu can't
// have a measure faster than that.
SUMX(
	FactTable,
	var Fee1 = RELATED( CRM[Fee % 1] )
	var Fee2 = RELATED( CRM[Fee % 2] )
	var EndDate = RELATED( CRM[End Date] )
	var Date_ = FactTable[Date]
	var Fee2IsBlank = ISBLANK( Fee2 )
	// TheFee codes the following logic:
	// IF Fee % 2 is BLANK, then Fee % 1, else
	// IF Date (from "UnionTable") is less or equal 
	// to Fee % 1 end Date then Fee % 1, else Fee % 2.
	var TheFee =
		var ChooseFee1 = (Date_ <= EndDate)
		var ChooseFee2 = 1 - ChooseFee1
		return
			Fee2IsBlank * Fee1
			+ (1 - Fee2IsBlank)
			* (
				Fee1 * ChooseFee1
				+ 
				Fee2 * ChooseFee2
			)
	var TheNetSpend = FactTable[NetSpend]
	var TheGrossSpend =
		DIVIDE( TheNetSpend, 1 - TheFee )
	return
		TheGrossSpend
)

[Anonymous]

// The Spend Data 1, 2, 3... should be
// one table as they have the same
// layout. Just use PQ to union them.
// You can add another column to this
// union that will tell you which table
// the data comes from.

// Based on this assumption:

[Gross Amount] = // measure that should be universal and fast
SUMX(
	VALUES( CRM[Fee %] ),
	var TheFee = CRM[Fee %]
	var TheNetSpend = 
		CALCULATE( SUM( UnionTable[NetSpend] ) )
	var TheGrossSpend =
		// Fees should be decimals in [0, 1).
		DIVIDE( TheNetSpend, 1 - TheFee )
	return
		TheGrossSpend
)

Using this setup you'll be also able to slice by the table. Something that is not achievable (at least easily) in your current model.

[devitus]

Hi @Anonymous your solution worked for what I'm trying to solve, but there is one more variable I have not mentioned and haven't managed to include in the DAX formula myself. Would you mind kindly assisting me with it to complete the measure?

The Fee % needs to vary depending on a date. The CRM table includes the following columns:

Fee % 1

Fee % 1 end Date (only present for accounts that changed a fee, otherwise BLANK)

Fee % 2 (only present for accounts that changed a fee, otherwise BLANK)

The logic for var TheFee should be the following:

IF Fee % 2 is BLANK, then Fee % 1, else

    IF Date (from "UnionTable") is less or equal to Fee % 1 end Date then Fee % 1, else Fee % 2.

Likewise, the calculation needs to take place on a row-by-row basis, which in my understanding means that the aforementioned logic should be enclosed within the SUMX() of this measure.

Thanks in advance!

[Anonymous · Accepted solution]

// The Spend Data 1, 2, 3... should be
// one table as they have the same
// layout. Just use PQ to union them.
// You can add another column to this
// union that will tell you which table
// the data comes from.

// Based on this assumption:

[Gross Amount] =
// This measure could be slow as it
// needs to iterate the fact table. If you
// want to have a blasingly fast one, you need
// to precalculate the gross spend in
// Power Query. It'll then be the fastest
// since it will only have to sum up
// a column with the gross spend. YOu can't
// have a measure faster than that.
SUMX(
	FactTable,
	var Fee1 = RELATED( CRM[Fee % 1] )
	var Fee2 = RELATED( CRM[Fee % 2] )
	var EndDate = RELATED( CRM[End Date] )
	var Date_ = FactTable[Date]
	var Fee2IsBlank = ISBLANK( Fee2 )
	// TheFee codes the following logic:
	// IF Fee % 2 is BLANK, then Fee % 1, else
	// IF Date (from "UnionTable") is less or equal 
	// to Fee % 1 end Date then Fee % 1, else Fee % 2.
	var TheFee =
		var ChooseFee1 = (Date_ <= EndDate)
		var ChooseFee2 = 1 - ChooseFee1
		return
			Fee2IsBlank * Fee1
			+ (1 - Fee2IsBlank)
			* (
				Fee1 * ChooseFee1
				+ 
				Fee2 * ChooseFee2
			)
	var TheNetSpend = FactTable[NetSpend]
	var TheGrossSpend =
		DIVIDE( TheNetSpend, 1 - TheFee )
	return
		TheGrossSpend
)

[devitus]

Thank you @Anonymous it has worked with the measure you have suggested! I can see that it slows down table rendering times, so I'll test its speed for a while and will revert to PQ option for Gross Spend if the measure proves unsustainable.

Thanks again!

[Anonymous · Accepted solution]

[Gross Amount] =
SUMX(
	SUMMARIZE(
		FactTable,
		FactTable[Date],
		CRM[Fee % 1],
		CRM[Fee % 2],
		CRM[End Date]
	),
	var Fee1 = CRM[Fee % 1]
	var Fee2 = CRM[Fee % 2]
	var EndDate = CRM[End Date]
	var Date_ = FactTable[Date]
	var Fee2IsBlank = ISBLANK( Fee2 )
	var TheFee =
		var ChooseFee1 = (Date_ <= EndDate)
		var ChooseFee2 = 1 - ChooseFee1
		return
			Fee2IsBlank * Fee1
			+ (1 - Fee2IsBlank)
			* (
				Fee1 * ChooseFee1
				+ 
				Fee2 * ChooseFee2
			)
	var TheNetSpend = 
		CALCULATE( SUM( FactTable[NetSpend] ) )
	var TheGrossSpend = 
		DIVIDE( TheNetSpend,  1 - TheFee )
	return
		TheGrossSpend
)

You could try the version above... might be faster but it depends on the data in your tables.

[devitus]

Thank you for your suggestion. I will test it and get back with findings!

Really appreciate your help so far.