Dividing 2 values in same column

[andybrace]

Hi all,

I have a table with Revenue values and a "multiplier" value.  I have added a "Manuallycalculated" column to show the correct value that I need to calculate - this is calculated at the row level and matches the first 4 columns and divides the matching Revenue (accountID = 1) with the multiplier (AccountID = 2). i.e. rows 1 and 7 match and 100 / 0.8 = 125.

I also have a simple account mapping table:

The two are connected with a relationship on the AccountID columns.

I am trying to show in a matrix the correct values using DAX measures:

Measure_Revenue =

Var _Account = "Revenue"

Var TotalRevenue =

CALCULATE(SUM(FactTable[Value]),

    FILTER(ALL(AccountMapping[AccountName]),AccountMapping[AccountName] = _Account))

RETURN

TotalRevenue

Measure_MultipliedRevenue =

Var _Account = "MultiplyFactor"

Var TotalRevenue = [Measure_Revenue] /

CALCULATE(SUM(FactTable[Value]),

    FILTER(ALL(AccountMapping[AccountName]),AccountMapping[AccountName] = _Account))

RETURN

TotalRevenue

This is not working as expected, I get:

 

This is because it is taking the total Revenue for CatA = 1 and / Total multiplier for CatA = 1... (100+51) / (0.80+0.75) = 97.42.

But, what I want it to do is calculate at the row level and then sum up ... (100/0.80) + (51/0.75) = 193

Any ideas how to adjust the DAX to do this?

[andybrace]

Thanks for this.. I haven't had time to try it yet but the second option looks perfect!  I will test it soon and mark it as accepted solution if I can get it to work!.

Thanks again!

[MFelix · Accepted solution]

Hi @andybrace ,

The problem on your calculation is that you need to have the exact order and that is not present on your calculation to do this I believe that the best option is to have a change on the semantic model making the multiplier column next to the corresponding revenue.

Do the following:

• Group by on Account
• Add a custom column for index for each group
• Expand the column
• Select the account and do unpivot by value
• Rename columns 1 and two to Revenue and Multiplier
• Add the measures below:

Measure_Revenue = 
SUM(FactTable[Revenue])

Measure_MultipliedRevenue = 
SUMX(FactTable, DIVIDE(FactTable[Revenue], FactTable[Multiplier]))

If you want to keep your logic has is you need to:

• Group by on Account
• Add a custom column for index for each group
• Expand the column

Now change your measure for the Multiplier to:

MULITPLIERNEW = 
    VAR tempt = ADDCOLUMNS(
		SUMMARIZE(
			'FactTable (DAX)',
			'FactTable (DAX)'[Index]
		),

		"Revenue", CALCULATE(
			SUM('FactTable (DAX)'[Value]),
			TREATAS(
				{
					1
				},
				'FactTable (DAX)'[AccountID]
			)
		),
		"Multiplier", CALCULATE(
			SUM('FactTable (DAX)'[Value]),
			TREATAS(
				{
					2
				},
				'FactTable (DAX)'[AccountID]
			)
		)
	)
	RETURN
		SUMX(
			tempt,
			DIVIDE(
				[Revenue],
				[Multiplier]
			)
		)


REVENUENEW = 
    VAR tempt = ADDCOLUMNS(
		SUMMARIZE(
			'FactTable (DAX)',
			'FactTable (DAX)'[Index]
		),

		"Revenue", CALCULATE(
			SUM('FactTable (DAX)'[Value]),
			TREATAS(
				{
					1
				},
				'FactTable (DAX)'[AccountID]
			)
		)
	)
	RETURN
		SUMX(
			tempt,
			
				[Revenue]
		)

See file attach