#DataIsights
LightNews LightNews
excelguru.ca
I'm wrapping up the week by teaching for a private client on writing DAX formulas for #Excel #PowerPivot.
So what is #DAX? It's the language used to create powerful measures that allow to aggregate your data and gain better #DataIsights. Here's a little challenge to properly generate cost by line. 🤓
What is DAX? Data Analysis eXpressions - Formula language used to create Measures - How we aggregate our data - Unbelievably powerful One Caution - Not all problems are DAX problems - Not all problems are Power Query - problems - If the DAX is too hard, reshape your data with Power Query - If you need dynamic aggregations, create formulas with DAX Challenge: Generate Cost by Line DAX Formulae: - Units on Hand: =SUM(Inventory[Units]) - Avg Cost: =AVERAGE(Inventory[Last Cost]) - Cost x Line: =[Units on Hand]*[Avg Cost] Problem: Grand Total doesn’t add to SUM of the lines above (Screenshot of PivotTable where the grand total of the Cost x Line column is 1,522.73 with a highlight noting: Shouldn’t this be 1,500.40?) How Iterator Functions Work Formulae: - Regular: =[Units on Hand]*[Avg Cost] - SUMX: =SUMX(Inventory,[Regular]) Example SUMX formula that correctly calculates the total: = SUMX( SUM(1) * AVERAGE(23.95) + SUM(2) * AVERAGE(24.95) + SUM(4) * AVERAGE(23.95) + SUM(4) * AVERAGE(24.95) + SUM(9) * AVERAGE(24.95) )
September 19, 2025 at 5:42 PM