Forum Discussion

andybrace's avatar
andybrace
Frequent Visitor
1 year ago
Solved

Dividing 2 values in same column

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?

Need Help
  • MFelix's avatar
    MFelix
    1 year ago

    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

     

     

     

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    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

     

     

     

  • andybrace's avatar
    andybrace
    Frequent Visitor
    1 year ago

    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!