DAX help with what was a simple Excel formula

[leekarensl]

Hi all,

I have a table with data that belongs to either category A or category B. Depending on the ROI of either product, I want to create a DAX that will calculate the proportion of resources being allocated to it. In the attached screenshot you can see that for product A with an ROI of 12, the formula is simply cell B2/ SUM(B2: B3) which will give the output of 0.6. How do I write this in DAX syntax in order to create a measure for Allocation ? Thanks!

[leekarensl · Accepted solution]

Hey I managed to solve the issue. I found a very useful post here on how to calculate totals for measures. So I created another measure as follows:

ROI.total =

var check = SUMMARIZE('SPEND_ALL', 'SPEND_ALL'[category], "aaa", [ROI])

return

if(ISINSCOPE('SPEND_ALL'[category]), [ROI], sumx(check, [aaa]))

I then use my previously calculated ROI and divided by this ROI.Total.

[Anonymous]

Hi @leekarensl ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[leekarensl]

Hey @Anonymous , no it didn't. Sorry I took so long to follow up on Greg's response. Was trying to figure out why it didn't work.

[Greg_Deckler]

@leekarensl Well, as a calculated column that would be:

Allocation (column) = DIVIDE( [ROI], SUM('Table'[ROI]) )

[leekarensl]

Thanks for the prompt response @Greg_Deckler , however the ROI is a measure which I had created on Power BI so it does not allow me to do a SUM. Is there another way? Thanks so much in advance.

[Greg_Deckler]

@leekarensl Well, depends a lot on context but you should be able to do this:

Allocation (column) = DIVIDE( MAX('Table'[ROI]), SUMX(ALLSELECTED('Table'), [ROI]) )

[leekarensl]

Thanks @Greg_Deckler unfortunately, it doesn’t seem to work as I got the error message Column 'ROI' in table ‘SPEND_ALL' cannot be found or may not be used in this expression. Based on what the error message says, it appears the ROI after the SUMX has got to be a column rather than a measure. I have tried putting the table name again:

Allocation (column) = DIVIDE(MAX(SPEND_ALL[ROI]), SUMX(ALLSELECTED(SPEND_ALL), SPEND_ALL[ROI]))

But I still got the same error message.

The closest that I got to the answer was with the following DAX.

Allocation = [ROI] / CALCULATE ([ROI]), ALLSELECTED(SPEND_ALL))

However, the sum of the ROI isn’t correct because rather than summing up all the ROIs in the selected rows, it is calculating the total ROI as total Revenue divided by total Costs. In the screenshot below the total ROI should be 17 (6.5 + 10.5) but it is taking 9.7 instead because revenue is 53,184 and costs is 5,480. As such the Allocation measure is giving the values of 0.67 and 1.08 respectively for Product A and B when it should be 0.38 and 0.62.

I have also tried creating a measure called Total.ROI which I then wanted to use this as the denominator.  In other words,

Total.ROI = calculate(
SUMX(SPEND_ALL, SPEND_ALL[REVENUE]/ SPEND_ALL[COSTS]))

And then

Allocation = [ROI]/ [TOTAL.ROI]. However it is not giving the right figures too. Can’t seem to see where I have gone wrong. Usually using SUMX is the way forward when calculating row by row but in this case it doesn’t seem to be working out. Any help will be appreciated, thanks in advance.