Forum Discussion
Converting Excel Formulas to DAX: SUMIF and VLOOKUP
Hi All,
I am trying to make several calculations in Power BI that were originally achieved using Excel formulas. However, after a couple days of research into DAX, I have spent long hours trying to figure out the correct DAX formula syntax for a few of the harder formulas, with little or no success. Now, in the interest of working smarter (and not harder) I humbly look to you PowerBI gurus for your extensive knowledge and expertise, and hopefully a quick solution, for the more complicated formulas 
Context:
There are two main data tables: Bookings, and Adjustments. On the Bookings table, I have structured some sales data to show Original Revenue, Original Gross Profit (Original GP), and Sale Type, based on the Sales Opportunity # (S/O #) which acts as the unique identifier. On the Adjustments table, we can see the columns Change in Revenue and Change in GP, which are plus/minus values against Original Revenue and Original GP, respectively. We also see the corresponding S/O # on the Adjustments sheet. I have also created a third table to store the list of unique S/O #'s to act as an index. This was my attempt at creating an ideal data architecture for the transition to leveraging Power BI visualizations, although I do not use Table 3 for my Excel formulas… Hopefully it helps you.
There are several calculated columns that do different things with the data. Here are some details:
All Tables:
1. Bookings (Table 1)
2. Adjustments (Table 2)
3: S/O # (Table 3)
Calculated Column: Original GP%
Table: Bookings
Purpose: Calculates Original GP / Original Revenue
Status: Working
Excel Formula: =[@[ORIGINAL GP]]/[@[ORIGINAL REVENUE]]
DAX Formula: = DIVIDE([ORIGINAL GP],[ORIGINAL REVENUE])
Data Type: Percentage
Calculated Column: Current Revenue
Table: Bookings
Purpose: Every time that the S/O# matches from Adjustments table, find sum of 'Adjustments'[Change in Revenue] and 'Bookings'[Original Revenue]
Status: Not Working – Don’t know the DAX syntax
Excel Formula: =[@[ORIGINAL REVENUE]]+SUMIF(Table 2[S/O '#],[@[S/O '#]],Table 2[CHANGE IN REVENUE])
DAX Formula: Unknown
Data Type: Currency
Calculated Column: Current GP
Table: Bookings
Purpose: Every time SO# matches from Adjustments table, calculate 'Adjustments'[Change in GP] against 'Bookings'[Original GP]
Status: Not Working – Don’t know the DAX syntax
Excel Formula: =[@[ORIGINAL GP]]+SUMIF(Table 2[S/O '#],[@[S/O '#]],Table 2[CHANGE IN GP])
DAX Formula: Unknown
Data Type: Currency
More Details: This is a basic breakdown of the Excel formula;
=[@[ORIGINAL GP]]+ SUMIF Add the Original GP value at the end of the formula if the following acceptance criteria is met
(Table2[S/O '#], Look at entire S/O# column on adjustments tab
@[S/O '#]], If it matches the SO # on this specific line
Table2[CHANGE IN GP] Then sum this column (the change in GP)
)
Calculated Column: Current GP %
Table: Bookings
Purpose: Divide Current GP / Current Revenue
Status: Working
Excel Formula: =[@[CURRENT GP]]/[@[CURRENT REVENUE]]
DAX Formula: = DIVIDE([CURRENT GP],[CURRENT REVENUE])
Data Type: Percentage
Calculated Column: Difference in Revenue
Table: Bookings
Purpose: Subtract Current Revenue - Original Revenue
Status: Not Working – Dependent on Current Revenue column
Excel Formula: =[@[CURRENT REVENUE]]-[@[ORIGINAL REVENUE]]
DAX Formula (My Best Guess): = CALCULATE(SUM(CURRENT REVENUE]))-Calculate (SUM([ORIGINAL REVENUE]))
Data Type: Currency
Calculated Column: Type
Table: Adjustments
Purpose: Populates the Type column on the Adjustments sheet using a simple VLookup.
Status: Not Working – Don’t know the DAX syntax
Excel Formula: =VLOOKUP([@[S/O '#]],Bookings!A:H,8,FALSE)
DAX Formula: Unknown
Data Type: Text
More Details: This is a basic breakdown of the Excel formula;
=VLOOKUP( Iterate through the following filter criteria
[@[S/O '#]], Lookup this value (SO# on this line)
'Bookings'!A:H, from the Bookings table and column range A through H
8, Return this column index # (which is the Sale Type column)
FALSE Must be an exact value, not a range
)
Sample Data:
https://www.dropbox.com/s/dsfun069h4atcvl/Sample1.xlsx?dl=0
Extra brownie points for explaining the DAX syntax and code logic (so I can actually learn), and for providing the cleanest code with the least potential impact to performance. Aaaaaand…GO 😊
Just a bit about myself – I am a SharePoint Solutions Engineer. I am certainly not an Excel or Power BI expert, but I am always eager to learn more!! Thank you!!
Thanks and Kind Regards,
Alex
2 Replies
Hi toxikshade,
Are you using a slicer on table3[S/O'#]? If you are, you can try this pattern:
Current Revenue = [ORIGINAL GP] + CALCULATE ( SUM ( Table2[CHANGE IN REVENUE] ), ALLSELECTED ( table3[S/O'#] ) ) Current GP = [ORIGINAL GP] + CALCULATE ( SUM ( Table2[CHANGE IN GP] ), ALLSELECTED ( table3[S/O'#] ) ) Difference in Revenue = [CURRENT REVENUE] - [ORIGINAL REVENUE] Type = LOOKUPVALUE ( table1[Name_Of_Column8], [S/O'#], "Value" )
Regards,
Jimmy Tao
Hi Jimmy, Thanks for the quick response! I worked through the different DAX formulas you provided, but was not able to get them to work as expected.
(Table1)Current Revenue and Current GP:
I am not using a slicer - could you maybe walk me through how you would structure this?
Current Revenue and Current GP values seem to sum the entire corresponding columns, and then add the Change in Revenue or Change in GP respectively.
I need row-level calculations, like this: Add Table1: Original GP to Table2:Change in GP if the corresponding SO# is found in Table3: S/O#. ALLSELECTED may be too broad an expression in this case, and instead I may need validation of SO#-to-SO#.
Here is an example of what the formula is actually doing to calculate Current Revenue (and Current GP) :
On Table 1, find S/O#: 11621607. (Observe that there is a positive value for Change in Revenue, and a negative value for Change in GP):
On Table 2, find the same S/O#.
Since it found the same S/O# on Table 2, it will add Table1:Change in Revenue + Table2:Original Revenue, thus resulting in the following Current Revenue value:
If there was no change in revenue, it would simply return the Original Revenue value since there was nothing added/subtracted. All of the above details also apply to Current GP. Note that each calculation is done at the row-level, so it will iterate through all rows with the same formula, but the input/output values should be different.
(Table2)Type:
The Type column is created to look up and return the Table1'[SALE TYPE] based on the corresponding Table3[S/O#] from that row. My guess is that the cardinality may not play well in this case, which is "1 to many" from "Table3 to Table 1" respectively. Both Table 1 and 2 have multiple possible values for Sale Type for each SO#. So basically when it looks up the SO# on Table1, I think DAX sees there are multiple SALE TYPE values possible for each SO#. Excel will conveniently pick one of the values, but I think PowerBI DAX gets caught up since there are multiple possible values. If anything, I would at least want to see the first Sale Type value if there happen to be multiple. A concatenation of both values, for example; Sale Type = MA / T, would also be acceptable.
Your coding did bring to light several of the mistakes I was making with DAX, and I am researching other expressions now to see if I can create the validation I need. I am certain that this is much simpler than I thought. I would be delighted if you wanted to continue working on this, and please let me know if you have any questions. Thank you for your help!
Alex
