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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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