Measure based off columns shown? (screenshot example included)

[Anonymous]

Hi folks,

So I figure this one is best explained with an example.  In the attachment, the total revenue of Project 30000020 is $60k.  However, since I've added EmployeeID, the one row expanded to four rows (one for each employee).  I would like the revenue calculation to spread across all four rows ($15k each) or show up as blank since I've included the Employee.  Right now, looking at the example, you may be inclined to believe Project 30000020 sums to $240k.  Please note that I simplified the details and would prefer not to mess with the active relationship/direction, although I'm ok with adding an inactive relationship.

Project Revenue Contains: ProjectID/Date/Fee/Status columns and the Forecast Revenue measure (Fee based off Status).  The fee/measure sum up by date.

Project Team Contains: ProjectID/EmployeeID columns and a # of Employees measure (uses AllSelected so it works like column)

TL;DR: Essentially, I need a validation function for determining if a column and/or table are being used when aggregating a measure.  I've tried HASONEVALUE with no luck...

As always - thank you!!!

[parry2k]

@Anonymous there are couple of ways to handle it, create a measure to divide revenue by each employee using measure, basically count the employee of the project and divide product amount by employee count and from there use this measure for all the calculations, or in power query get the total count of employees (similar approach as above) and distribute the amount. You have a few options to handle it.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡