Help with a DAX measure

[GiaD30]

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.

[DataNinja777]

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,

[GiaD30]

@DataNinja777  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?

[GiaD30]

@danextian hello and thank you for your reply. Unfortunately I am not able to upload the pbix , but I will try to better explain.

So, the initial excel file that I have added in power bi is this below, named test:

Then , I have calculated some measures, Net salary, Cars benefit, Books Benefit, Games Benefit.

These are the dax measures:

Net salary = SUM(test[Gross salary])*0.925

Cars benefit = CALCULATE(SUM(test[Total money spent)*0.8/0.7, FILTER(test, test[Toys]= "Cars"))

Books benefit = CALCULATE(SUM(test[Total money spent)*0.9/0.8, FILTER(test, test[Toys]= "Books"))

Games benefit = CALCULATE(SUM(test[Total money spent)*0.7/0.6, FILTER(test, test[Toys]= "Games"))

Then my table created in power bi is like that, the red line is with the measure :

Now, I need to create aa final measure,  the formula is this :

Final measure = Net salary - (Cars benefit+Books benefit+Games benefit)

But, the value , I need to appear in the row, where I also have the net salary, basically, this measure, I need to calculate an indicator, by each name, considering all the toys.

So, I need to show like that in my table:

[danextian]

Hi @GiaD30 

You need to be able to assign Net/Gross salary to the each individual [Name] such that each record returns the same salary assigned to that specific name.

Net Salary = 
CALCULATE (
    SUM ( 'test'[Gross salary] ) * 0.925,
    ALLEXCEPT ( test, test[Name] )
)

After that, total deductions from the net salary are to be assigned to all toys rows of [Name] and not just to their relevant rows.

Finally, if you want it to look like in your excel, DAX must be able to identify which rows in your table is the first one for each [Name] and so you create an index column in the query editor.

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 row corresponds to the first index for the current Name
        IF (
            SELECTEDVALUE ( test[Index] )
                = CALCULATE ( 
                    -- Get the minimum Index value for the current Name
                    MIN ( test[Index] ), 
                    ALLEXCEPT ( test, test[Name] )
                ),
            -- If it is the first index for the Name, return _fm
            _fm
        )
    )

Please note that I changed the formulas of your benefits measures to use DIVIDE as a best practice and KEEPFILTERS when filtering just a single column without applying the filtered value to the whole column - as a best practice you should only filter a column when filtering a column and not a table. This explicitly creates a new filter table where only rows satisfying test[Toys] = "Books" are included. It doesn't filter just the [Toys] column.

FILTER(test, test[Toys]= "Books"))

Please see the attached sample pbix.

[GiaD30]

@danextian  thank you so much for the solution, it worked indeed. The only question I still have, is it possible , to see the value for the final measure on the same row as gross salary? I am asking this, cause i don't always have the gross salary value added on the first row, from the 3, 4 etc names I have and I wouls like to keep consistency in the table.

[danextian · Accepted solution]

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
        )
    )

[GiaD30]

@danextian  thank you very much, it worked:)