cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Measure based off columns shown? (screenshot example included)

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

revenue_oktodelete.png

 

 

1 REPLY 1
Highlighted
Super User VII
Super User VII

Re: Measure based off columns shown? (screenshot example included)

@bflury 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.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

August Community Highlights

Check out a full recap of the month!

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors