Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
chein
Frequent Visitor

Average of Measure Total when counting active projects

Hello everybody,

 

this is my first post so I´m sorry if I make some mistakes posting my Problem.

 

Problem.PNG

 

In my Power BI file I have two tables: "Consultants" with the name of a consultant, a start- and an end-date and "calender" with dates form January 1st 2016 till December 31st 2020.

I have a measure that counts "active" consultants which is defined like this: 

Active Consultants = CALCULATE (COUNTROWS ( Consultants );FILTER (Consultants;(Consultants[Start] <= LASTDATE ( calender[Date] )&& Consultants[End] >= FIRSTDATE ( calender[Date] ))))
 
My second measure just counts the days of the calender:
Days = COUNTROWS(calender)
And the third one multiplies my two other measures:
Days x Active Consultants = [Days]*[Active Consultants]
 
What I´m trying to achieve is to get the total of a year for "Days x Active Consultants" as the sum of the line/different months. I know, that the logic behind the measure doesn´t allow the total to be the total of the line. But I couldn´t find a solution to my problem in this forum. I found a lot of threads where people fixed similar problems with SUMMARIZE, but I don´t think this is working in my case. One approach could be to get the average of the "Active Consultants" as total of the year, but again, I have no clue how to to it and I think SUMMARIZE won´t help.
 
Does anybody have an idea on how to solve this problem.
 
Thanks in advance
Chris
 
2 REPLIES 2
hthota
Resolver III
Resolver III

Do you need the total duration of 3rd measures in Years'

 

Is I am correct. if not please correct me,

 

Thanks,

Hemanth Thota.

chein
Frequent Visitor

Hi @hthota ,

 

Problem-Detail.PNG

No, thats not what i want. I want the red outlined value to be the total of the line "Days x Active Consultants" (so in fact 11377 + 10640 + 12028 ... = 108169). But I don´t think that I can adjust this measure to calculate the total right. So the first step to get into the right direction in my eyes would be to get the green outlined value to be the average of the line "Active Consultants".

 

Regards,

Chris

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.