Forum Discussion
Helper IIHelp with a DAX measure
Hello everyone and Happy New Year,
I need your help, please, with a situation.
I have the below table, with name, gross salary, toys and total money spent.
Net salary, Cars benefit, Books benefit and Games benefit are some measures I will add in power bi.
Now, the important thing is that, on each name, the gross salary and net salary will appear only once, not everytime when the name appears in the table.
Also, the values for the measures Cars benefit, Games benefit and Books benefit, will appear only when the names will appear in the column Toys, like you see in the table.
Based on this, I need to calculate a final measure, like in the table, the formula is: Net salary- Cars benefit-Books benefit-Games benefit, but I need the result to appear also only once, where the gross and net salary appear in the table.
Basically the final measure is like an indicator by Name, to check a total view for each Name.
Thank you so much.
Hi GiaD30
You can instead check whether Gross salary is blank in the current row
Final measure = VAR _fm = -- Calculate the net salary after deducting specific benefits for each Name. [Net Salary] - CALCULATE ( -- Sum of benefits for Cars, Books, and Games [Cars benefit] + [Books benefit] + [Games benefit], -- Remove all filters except for the Name column ALLEXCEPT ( test, test[Name] ) ) RETURN -- Check if there is more than one value in the Name column in the current context IF ( NOT ( HASONEVALUE ( test[Name] ) ), -- If there are multiple names, return the calculated _fm _fm, -- Otherwise, check if the Gross salary is not blank IF ( NOT ( ISBLANK ( SELECTEDVALUE ( test[Gross salary] ) ) ), _fm ) )
8 Replies
- DataNinja777
Super User
Hi GiaD30 ,
To calculate the Final Measure in Power BI using DAX, you need to account for each person's total money spent, their benefits from different categories (Cars, Books, and Games), and their gross salary. This can be done by using DAX measures that dynamically calculate these values based on the dataset.
First, create a measure to calculate the total money spent by each individual. This can be achieved by using the SUM function on the Total money spent column and grouping by each person's name. The SUMMARIZE function helps in summarizing the total money spent across all expense types for each person.
TotalMoneySpent = SUMMARIZE( 'Expenses', 'Expenses'[Name], "Total Spent", SUM('Expenses'[Total money spent]) )Next, create a measure to calculate the total benefits from Cars, Books, and Games for each individual. The SUM function will add up the benefits for each category, and the SUMMARIZE function will group them by name.
Benefits = SUMMARIZE( 'Expenses', 'Expenses'[Name], "Cars Benefit", SUM('Expenses'[Cars benefit]), "Books Benefit", SUM('Expenses'[Books benefit]), "Games Benefit", SUM('Expenses'[Games benefit]) )To calculate the net salary, subtract the total money spent from the gross salary. The MAX function ensures that you retrieve the gross salary for each person, and the CALCULATE function applies the necessary filters to get the correct values.
NetSalary = CALCULATE( MAX('Expenses'[Gross salary]) - [Total Spent] )The final measure is calculated by subtracting the total benefits from the net salary. This measure considers all benefits and adjusts the net salary accordingly to get the final result.
FinalMeasure = NetSalary - ( SUM(Benefits[Cars Benefit]) + SUM(Benefits[Books Benefit]) + SUM(Benefits[Games Benefit]) )Alternatively, you can create a single DAX measure to calculate the Final Measure using variables to store intermediate values for total money spent, benefits, and gross salary. The RETURN statement then calculates the final result by subtracting these values appropriately.
Final Measure = VAR TotalSpent = SUM('Expenses'[Total money spent]) VAR CarsBenefit = SUM('Expenses'[Cars benefit]) VAR BooksBenefit = SUM('Expenses'[Books benefit]) VAR GamesBenefit = SUM('Expenses'[Games benefit]) VAR GrossSalary = MAX('Expenses'[Gross salary]) RETURN GrossSalary - TotalSpent - (CarsBenefit + BooksBenefit + GamesBenefit)This final measure will provide the desired result in a Power BI report, adjusting dynamically based on the dataset and ensuring that the correct net salary is calculated after accounting for total expenses and benefits.
Best regards,
GiaD30DataNinja777 Thank you very much for your reply, but , in my table, Cars benefit, Books benefit and Games benefit are already some measures I calculated, these are not columns.
- danextian
Could you please provide us with a sanitized copy of your PBIX so we can better understand your semantic model instead of guessing and potentially missing the mark?
