cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

Need to Filter across whole model and divide by a count

Hi, 

I'm fairly new to Power BI and am struggling with a calculated column. I have attached a picture of my model below. My MergedTable contains logged expecteds and actuals for time spent on projects. The Team Leave table contains data about when members are on holiday, study etc. and my Leave Mappings contains more info the main one being whether the employee is actually working or not (Team Leave table has data regarding conferences, away days etc.). 

 

I am trying to create a column in the MergedTable called Expected Time that will multiply the Time column by the count of how many days an employee is Working in a week from the Team Leave table divided by 5. So every employee has 5 entries a week in the Team Leave table with 1 Week_Ending value. If they have a code which means they are not working for 2 days that week (from Leave Mappings) then I want to multiply my Time column in MergedTable by 3/5 to give my new column. 

Any help would be appreciated including alternative methods.

Thanks,

 

 

 

 

 

 

DataModel.PNG

1 REPLY 1
ibarrau
Super User
Super User

Hi. Before answering please consider reading about data modeling to learn about star schema and good practise to avoid things like bidirectional filter.

If you want to add a column in a table related to another one, you have to consider first if the relations for each row is one or more in order to understand if an aggregation is needed. After reading your example I think you need an aggregation. Supose you need to count rows, it should look like this:

NewColumn =
COUNTX ( 
    RELATEDTABLE(TableToCount) ,
    TableToCount[Column]
)

This will help you to add in MergedTable rows related from TeamLeave or you can do it in opposite way.

Hope this helps.

Regards,

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors